OraFAQ Forum: Data Integration » Using SQL Select as Source for a Mapping

Home » Developer & Programmer » Data Integration » Using SQL Select as Source for a Mapping (Warehouse Builder 10.2.03)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Using SQL Select as Source for a Mapping [message #298828]

Thu, 07 February 2008 22:21

ianh
Messages: 1
Registered: February 2008

Junior Member

Hi,
I'd like to be able to use a select statement in warehouse builder as a data source in a mapping. SQL shown below.

Can this be done or do I have to create a view outside of warehouse builder and import it?

SQL

SELECT   s.School_Id,
         Nvl(MAX(DECODE(cv.Cat_Id,'UCL',cv.Long_desc,
                                  NULL)),'Unknown') ucl,
         Nvl(MAX(DECODE(cv.Cat_Id,'REGION',cv.Long_desc,
                                  NULL)),'Unknown') Region,
         Nvl(MAX(DECODE(cv.Cat_Id,'TREASREG',cv.Long_desc,
                                  NULL)),'Unknown') treAsreg,
         Nvl(MAX(DECODE(cv.Cat_Id,'RELGN',cv.Long_desc,
                                  NULL)),'Unknown') relgn,
         Nvl(MAX(DECODE(cv.Cat_Id,'NGEFF',cv.Long_desc,
                                  NULL)),'Unknown') ngeff,
         Nvl(MAX(DECODE(cv.Cat_Id,'PCAP',cv.Long_desc,
                                  NULL)),'Unknown') pCap,
         Nvl(MAX(DECODE(cv.Cat_Id,'ATSIC',cv.Long_desc,
                                  NULL)),'Unknown') Atsic
FROM     sdw.sdr_Schools s,
         sdw.sdr_School_Category_Values scv,
         sdw.sdr_Category_Values cv
WHERE    s.School_Id = scv.School_Id (+) 
         AND cv.Cat_Id = scv.Cat_Id
         AND cv.Cat_Value_Id = scv.Cat_Value_Id
GROUP BY s.School_Id
ORDER BY s.School_Id;

[Updated on: Thu, 07 February 2008 22:33]

Report message to a moderator

Re: Using SQL Select as Source for a Mapping [message #349101 is a reply to message #298828]

Thu, 18 September 2008 21:33

miry1980
Messages: 2
Registered: September 2008
Location: Chicago

Junior Member

Create VIEW AS
SELECT * FROM
SELECT s.School_Id,
Nvl(MAX(DECODE(cv.Cat_Id,'UCL',cv.Long_desc,
NULL)),'Unknown') ucl,
Nvl(MAX(DECODE(cv.Cat_Id,'REGION',cv.Long_desc,
NULL)),'Unknown') Region,
Nvl(MAX(DECODE(cv.Cat_Id,'TREASREG',cv.Long_desc,
NULL)),'Unknown') treAsreg,
Nvl(MAX(DECODE(cv.Cat_Id,'RELGN',cv.Long_desc,
NULL)),'Unknown') relgn,
Nvl(MAX(DECODE(cv.Cat_Id,'NGEFF',cv.Long_desc,
NULL)),'Unknown') ngeff,
Nvl(MAX(DECODE(cv.Cat_Id,'PCAP',cv.Long_desc,
NULL)),'Unknown') pCap,
Nvl(MAX(DECODE(cv.Cat_Id,'ATSIC',cv.Long_desc,
NULL)),'Unknown') Atsic
FROM sdw.sdr_Schools s,
sdw.sdr_School_Category_Values scv,
sdw.sdr_Category_Values cv
WHERE s.School_Id = scv.School_Id (+)
AND cv.Cat_Id = scv.Cat_Id
AND cv.Cat_Value_Id = scv.Cat_Value_Id
GROUP BY s.School_Id
ORDER BY s.School_Id;

and MAP Into Target

Report message to a moderator

Previous Topic:	Error API8003
Next Topic:	How to run mappings from Unix ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jan 13 19:56:53 CST 2025