Rewrite Subselect as Join

[deedug]

The intent of the following SQL statement is to obtain "STRING" (RS.C_STR_ID,RS.C_POS_ID,RS.C_PRTY_SORT) information for a given "REGION" (RS.C_RGN_ID) and customer (AC.C_ACCT_CST ). Only strings with all known/supplied
"components" are returned from the correleated subselects.

This is a processing "HOG". I read that I could rewrite a subselect as a join query but when I try this, I get more than I expect.

Does anyone out there have any suggestions!!

SELECT RS.C_POS_ID,RS.C_STR_ID, RS.C_PRTY_SORT
FROM WGCA0P01.CA0T026_RGN_STR RS
WHERE RS.C_RGN_ID = :XXX
AND NOT EXISTS
(SELECT * FROM WGCA0P01.CA0T042_STR_CMPN SC
WHERE RS.C_STR_ID = SC.C_STR_ID
AND NOT EXISTS
(SELECT * FROM WGCA0P01.CA0T040_ACCT_CMPN AC
WHERE SC.C_CMPN_ID = AC.C_CMPN_ID
AND AC.C_ACCT_CST = :XXXXX))
ORDER BY RS.C_RGN_ID, RS.C_STR_ID, RS.C_PRTY_SORT;

Thanks

 

[MarcLodge]

Hi deedug,
You can re-write subselects as joins, but in your case I don't think this is applicable. You are looking for rows on the primary tables, where they do not exist on subsequent tables. When you join tables, it is no data that exists rather than not exists.

I don't therefore think there's a way around it, but am happy for somebody else to prove me wrong.
hth
Marc

 

[Aundrea]

Try using "Left Exception join" - this will return rows from the left table that do not EXIST in the right table.

 

[blom0344]

Hello Deedug,

Are you sure your query is making sense in the first place? Youre primary table RS is joined with CS which itself is joined with AC. I can imagine that if AC and CS were joined to RS your query would in any way be a sensible one.
What is the thought behind this construction?

For test purposes I can imagine creating a resultset from the CS - AC part as a database-view and then creating one outputfield with distinct values for SC.C_STR_ID
Subsequently doing the non matching stuff over this smaller recordset. Just an imaginative suggestion......... T. Blom
Information analyst
tbl@shimano-eu.com