complex nested inner joins

[Former Member]

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

[Former Member]

Hi Smitha,

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

Regards,

Satish

[Former Member]

You should try an OUTER JOIN for that table.

Rob

[Former Member]

Hello Rob,

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

Regards,

Smitha

[Former Member]

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