Catalog Joins

[gandhids]

Hi,
I have 5 tables A,B,C,D,E
A
Investment_detail_ID

B
Investment_detail_ID
Investment_id
c

Investment_id
Deal_id

D
Deal_id

E
Deal_ID

on Joining the A,B,C,D I get some deal_ids.Now if I have to find out which ids of these result set are not there in table of e, how can join tables??Currently I have A and B joined on Investment_detail_id, B and C joined on Investment_id , c and d joined on deal_id, and now how shall i join d and e.If i join them on deal_id, i miss those deal_id which i get from the combination of A,B,C,D.Can outer join be the solution.
If so,How??which tables shall i perform outer joins on.My report will require the data fromtable A and E.
Thanks

 

[nagrajm]

In the catalog-join between D and E, place the outer join check mark on D. This will force all rows that satisfy A,B,C,D to display with NULL (MISSING VALUES) for E.

 

[gandhids]

Hi,
Thanks for your reply.I tried doing the same.But i forgot to tell you one thing.I have two fields called balance_month and balance_year in both the tables A and E.In filter i put the condition as follows
A.balance_month =03 nad A.Balance_Year=2003 and E.balance_month =03 nad E.Balance_Year=2003 .Once i put this, i guess, it nullifies the effect of outer join.If i don't put this condition. i get multiple records.

waiting for your reply

 

[gandhids]

the above problem gets resolved if by any way i could get my query to do as follows
D."DEAL_ID"=E."DEAL_ID" and
E."BALANCE_MONTH"(+)=03 and
E."BALANCE_YEAR"(+)=2003 and
When i specify outer join between D and E, still i need to have some arrangement for balance_month and Balance_year as above.How do i acieve it at catalog level.Month 03 and Year 2003 i have taken for a particular month.

 

[nagrajm]

Further to my above thread for outer join condition at the catalog level, construct the following filter condition and this should resolve your issue;

A.balance_month = 03 and A.Balance_Year=2003 and ((E.balance_month =03 or E.balance_month is missing) and (E.Balance_Year=2003 or E.Balance_Year is missing))

It is not advisable to build this filter condition at the catalog level.

 

[gandhids]

Hi nagrajm,
Yor suggestion seems to be quite helpful.See, i should have got three missing deals whereas i am getting only one deal when i modify the query as u suggested.here is the query, may be u can have a look over it...what could be the reasons...Please revert.
select "c8" "c1", "c7" "c2", "c5" "c3", "c6" "c4"
from (select count(distinct T1."DEAL_ID&quot "c5"
from "EDW_ODS"."INVESTMENT" T4, "EDW_ODS"."INVESTMENT_DETAIL" T5, "EDW_ODS"."INVESTMENT_DETAIL_BALANCES" T2, "EDW_ODS"."DEAL" T1, "EDW_ODS"."DEAL_BALANCES" T3
where T1."DEAL_ID"=T3."DEAL_ID"(+) and T1."DEAL_ID"=T4."DEAL_ID" and T4."INVESTMENT_ID"=T5."INVESTMENT_ID" and T2."INVESTMENT_DETAIL_ID"=T5."INVESTMENT_DETAIL_ID" and (T3."BALANCE_MONTH"=3 or T3."BALANCE_MONTH" is null) and (T3."BALANCE_YEAR"=2003 or T3."BALANCE_YEAR" is null) and T2."BALANCE_MONTH"=3 and T2."BALANCE_YEAR"=2003) D2, (select T3."DEAL_ID" "c6", T2."INVESTMENT_DETAIL_ID" "c7", T1."DEAL_ID" "c8"
from "EDW_ODS"."INVESTMENT" T4, "EDW_ODS"."INVESTMENT_DETAIL" T5, "EDW_ODS"."INVESTMENT_DETAIL_BALANCES" T2, "EDW_ODS"."DEAL" T1, "EDW_ODS"."DEAL_BALANCES" T3
where T1."DEAL_ID"=T3."DEAL_ID"(+) and T1."DEAL_ID"=T4."DEAL_ID" and T4."INVESTMENT_ID"=T5."INVESTMENT_ID" and T2."INVESTMENT_DETAIL_ID"=T5."INVESTMENT_DETAIL_ID" and (T3."BALANCE_MONTH"=3 or T3."BALANCE_MONTH" is null) and (T3."BALANCE_YEAR"=2003 or T3."BALANCE_YEAR" is null) and T2."BALANCE_MONTH"=3 and T2."BALANCE_YEAR"=2003) D1
order by "c4" desc

 

[nagrajm]

When I compared your A,B,C,D,E tables with T1,T2,T3,T4,T5 I inferred;

A=T2
B=T5
C=T4
D=T3
E=T1

The joins you had wanted to establish was

A=B T2=T5
B=C T5=T4
C=D T4=T3
D(+)=E T3(+)=T1

However, I see the following join that is not represented above;

T1."DEAL_ID"=T4."DEAL_ID"

The above join should instead be

T3."DEAL_ID"=T4."DEAL_ID"

I think this should resolve your issue.

 

[gandhids]

Thanks for your reply...
here is the corrected matching....could you please suggest now where i missed..
A=T2
B=T5
C=T4
D=T1
E=T3

 

[nagrajm]

Your joins seem OK. I do not see any apparent errors. Perhaps, you should re-check your output to see if it indeed satisfies all your business rules!!!

I do not know if you should rewrite the following condition from (shouldn't make any difference really).

(T3."BALANCE_MONTH"=3 or T3."BALANCE_MONTH" is null) and (T3."BALANCE_YEAR"=2003 or T3."BALANCE_YEAR" is null) and T2."BALANCE_MONTH"=3 and T2."BALANCE_YEAR"=2003)

to

((T3."BALANCE_MONTH"=3 and T3."BALANCE_YEAR"=2003) or (T3."BALANCE_MONTH" is null and T3."BALANCE_YEAR" is null)) and T2."BALANCE_MONTH"=3 and T2."BALANCE_YEAR"=2003

Sorry, could not help beyond this. Let me know if anything worked.