topic Re: complex nested inner joins in Application Development and Automation Discussions

complex nested inner joins

Former Member — Tue, 20 Nov 2007 16:33:23 GMT

Hello Experts!

I am using a select statement with a complex structure of inner joins. This is how my select statement is written-

SELECT DISTINCT

s1~/bic/ghaprod5

s2~/bic/ghaprod7

s3~/bi0/9alocno

INTO TABLE

tb_data

FROM

/bic/fhakc_ofc AS f

INNER JOIN

/bic/dhakc_ofc1 AS d1

ON f_{key_hakc_ofc1 EQ d1}dimid

INNER JOIN

/bic/dhakc_ofc3 AS d3

ON f_{key_hakc_ofc3 EQ d3}dimid

INNER JOIN

/bic/sghaprod5 AS s1

ON d1_{sid_ghaprod5 EQ s1}sid

INNER JOIN

/bic/sghaprod7 AS s2

ON d1_{sid_ghaprod7 EQ s2}sid

INNER JOIN

/bi0/9aslocno AS s3

ON d3_{sid_9alocno EQ s3}sid

INNER JOIN

/bic/pghaprod7 AS p1

ON s1_{/bic/ghaprod5 EQ p1}/bic/ghaprod5

WHERE

p1~/bic/ghaplev1 IN s_ba

ORDER BY

s1~/bic/ghaprod5

s2~/bic/ghaprod7

s3~/bi0/9alocno .

I have the following problem-

If I use only the final where condition on the table /bic/pghaprod7, a particular record with /bic/ghaprod5 = 109246507 is not pulled into the destination table (This is the desired result as this record does not satisfy the where condition). However, if I use this complex select statement(along with the final where condition) the destination table has the record with /bic/ghaprod5 = 109246507. Why is this happening? Isn't the where condition being applied to the final result in the destination table?

Any help on this will be greatly appreciated.

Thanks and Regards,

Smitha

Re: complex nested inner joins

Former Member — Tue, 20 Nov 2007 16:54:07 GMT

Hi Smitha,

Use For all entries instead of Inner Join and get data seperately from each table.

Regards,

Satish

Re: complex nested inner joins

Former Member — Tue, 20 Nov 2007 17:05:09 GMT

You should try an OUTER JOIN for that table.

Rob

Re: complex nested inner joins

Former Member — Tue, 20 Nov 2007 18:30:32 GMT

Hello Rob,

Thank you for your reply. An outer join on which table should be done to get the desired result?

Regards,

Smitha

Re: complex nested inner joins

Former Member — Tue, 20 Nov 2007 18:53:22 GMT

Actually, I misread your question. I thought you were not getting it in the JOIN.

The difference in performance between JOINs and FOR ALL ENTRIES is not that great. I find FOR ALL ENTRIES to be easier to use and it's (to me) clearer what's going on.

So you may want to try the ealrier suggestion.

Rob