Need PX design for this scenario

[HemaV]

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,

[ray.wurlod]

Put user-defined SQL into an appropriate stage type.

[Nsg]

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

[Jayanto]

@HemaV :: The simplest solution will be 'As Ray suggested'.... To build a user defined query, in an appropriate DS Stage.

Breaking in further, will prolong the reach to solution.

[prajesh]

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