Home » Developer & Programmer » Warehouse Builder » Using SQL Select as Source for a Mapping (Warehouse Builder 10.2.03)
Using SQL Select as Source for a Mapping [message #298828] Thu, 07 February 2008 22:21 Go to next message
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 Go to previous message
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
Previous Topic: Error API8003
Next Topic: How to run mappings from Unix ?
Goto Forum:
  


Current Time: Fri Apr 18 09:29:31 CDT 2014

Total time taken to generate the page: 0.11966 seconds