Hi All,
I'm new to DS PX. Could you please suggest me the design for this scenario. I'm having huge records so wanted to build the job thinking about the performance also.
SQL Query:
SELECT * FROM ABC WHERE (aaa,bbb,ccc) IN (
SELECT aaa,bbb,ccc FROM ABC WHERE
STATUS = 'CLOSE' AND FLAG='Y' AND TRUNC((sysDate-STATDATE) / 365) >= 1
MINUS
SELECT aaa,bbb,ccc FROM DEF WHERE LVL <> 0
)
Needed DS PX design for this sql query.
Thanks in Advance,
Need PX design for this scenario
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Need PX design for this scenario
I would split teh query -
perform 2 extracts -
for table ABC -
SELECT aaa,bbb,ccc FROM ABC WHERE
STATUS = 'CLOSE' AND FLAG='Y' AND TRUNC((sysDate-STATDATE) / 365) >= 1
for table DEF
SELECT aaa,bbb,ccc FROM DEF WHERE LVL <> 0
and the do Lookup / merge (based on volume) and reject the records the donot match .... the rejected data is the data you are looking for ... ignore what matched.
Hope this helps.
Ng
perform 2 extracts -
for table ABC -
SELECT aaa,bbb,ccc FROM ABC WHERE
STATUS = 'CLOSE' AND FLAG='Y' AND TRUNC((sysDate-STATDATE) / 365) >= 1
for table DEF
SELECT aaa,bbb,ccc FROM DEF WHERE LVL <> 0
and the do Lookup / merge (based on volume) and reject the records the donot match .... the rejected data is the data you are looking for ... ignore what matched.
Hope this helps.
Ng
hi,
both ABC,DEF tables are same database or different.if same DB you can prefer user defined query.
If both tables are from different db you can go through px design and you mentioned huge amount of data.
so you can better design with join stage using leftouter.
abc--->sort---->join(leftouter)---->target
|
|
sort
|
|
def
both ABC,DEF tables are same database or different.if same DB you can prefer user defined query.
If both tables are from different db you can go through px design and you mentioned huge amount of data.
so you can better design with join stage using leftouter.
abc--->sort---->join(leftouter)---->target
|
|
sort
|
|
def
prajesh