approach to sql query in informatica 1

[aravindv26]

Hi all,

I have query is having 2 union statements, referencing 4 tables, and select statement is having sum functions, how to approach it in informatica.

my sql query is:
SELECT component_num,sum(psum),sum(dsum),sum(lsum)
FROM ( SELECT 'P',
component_num as component_num,
sum(line_amt+item_tax) as psum,
0 as dsum,
0 as lsum
FROM X
WHERE fiscal_week in ( SELECT distinct FISCAL_WEEK
FROM Y
WHERE TODAY = NEXT_DAY(TRUNC(SYSDATE)-7,'SUNDAY'))
AND ORG_DESCRIPTION not in ('GEL e-Pcard')
AND LR.LR_SCC_SSP_LLC_PARIS.ACCOUNT_NUMBER IN
('641200','641300','641301','641304','641310','642721','642727',
'642729','645101','645111','645121','645131','645210','645211',
'645294','645700','645703','645706','645707','645711','645721',
'645735','645780','645781','645782','645784','645785','680000',
'680100','680110','680113','680114','680115','680123','680125',
'680130','680165','680170','680193','680205','680704','680705',
'641311','645310','645412','645414','645415','645602','645704',
'645708','645716','645730','645750','646610','646614','646615',
'680111','680118','680120','680132','680199','680201','680204',
'680210','680220','680231','680249','680269','680299','680706')
GROUP BY fiscal_week, component_num
UNION
SELECT 'S',
TO_NUMBER(num_comp) as component_num,
0,
sum(SUM_AMT_EXTD_USD) as dsum,
0 as lsum
FROM Z
WHERE fiscal_week in ( SELECT distinct FISCAL_WEEK
FROM Y
WHERE TODAY = NEXT_DAY(TRUNC(SYSDATE)-7,'SUNDAY'))
AND (NUM_ACCT_ANC IN ('A00973','A00975') OR NUM_ACCT_ANC is NULL)
GROUP BY num_comp
UNION
SELECT 'L',
TO_NUMBER(a.num_comp),
0,
0,
sum(a.SUM_AMT_EXTD_USD)
FROM Z a,
U b
WHERE a.fiscal_week in ( SELECT distinct FISCAL_WEEK
FROM LR.LR_FISCAL_CAL
WHERE TODAY = NEXT_DAY(TRUNC(SYSDATE)-7,'SUNDAY'))
AND (NUM_ACCT_ANC IN ('A00973','A00975') OR NUM_ACCT_ANC is NULL)
AND a.nam_splr_prch = b.nam_splr_prch
GROUP BY a.num_comp
)
group by component_num

Thanks in advance...

 

[blom0344]

Aravind,

Do yourselve a favor and wrap the SQL into a database view.
Then use the view as a source for the mapping.
This is an elegant way to solve this one...

Ties Blom
Information analyst