Help required with 5 table join

[peskyangel]

I have 5 DB2 tables, all with the same unique primary key.
That key is SEGMENT, CORP and ACCOUNT.

I can have 1 row for a given SEGMENT, CORP and ACCOUNT on all 5 of these tables, or 1 row on 4 of them and not the 5th, 1 row on 3 of them and not the other 2, 1 row on 2 of them and not the other 3, etc. etc.
There might not even be a row on any of the tables for a given SEGMENT, CORP and ACCOUNT.

I am interested in returning the values of two columns from each of these tables.

At the moment, my online COBOL program reads the tables consecutively. If it finds a row on the table, it populates details in a data block. If it doesn't find a row, these details are set to spaces. If there's another SQLCODE the program abends.

I would like to read the tables concurrently.

The SQL I've written to do this is as follows.

SELECT T1.STATUS_TYP, T1.STATUS_START_TS
,T2.STATUS_TYP, T2.STATUS_START_TS
,T3.STATUS_TYP, T3.STATUS_START_TS
,T4.STATUS_TYP, T4.STATUS_START_TS
,T5.STATUS_TYP, T5.STATUS_START_TS
FROM BASSDEV.BRC_DECEASED T1
RIGHT OUTER JOIN BASSDEV.BRC_DEFAULT T2
ON T1.SEGMENT = T2.SEGMENT
AND T1.CORP = T2.CORP
AND T1.ACCOUNT = T2.ACCOUNT
RIGHT OUTER JOIN BASSDEV.BRC_GONE_AWAY T3
ON T1.SEGMENT = T3.SEGMENT
AND T1.CORP = T3.CORP
AND T1.ACCOUNT = T3.ACCOUNT
RIGHT OUTER JOIN BASSDEV.BRC_EXCEPTION T4
ON T1.SEGMENT = T4.SEGMENT
AND T1.CORP = T4.CORP
AND T1.ACCOUNT = T4.ACCOUNT
RIGHT OUTER JOIN BASSDEV.BRC_EXCEPTION T5
ON T1.SEGMENT = T5.SEGMENT
AND T1.CORP = T5.CORP
AND T1.ACCOUNT = T5.ACCOUNT
WHERE (T1.SEGMENT = '01'
AND T1.CORP = '10'
AND T1.ACCOUNT = '4775965306368373')

Now this SQL returns 1 row containing the information I require when SEGMENT '01', CORP '10' and ACCOUNT '4775965306368373' is present on T1, and one, two, three or all four of the other tables.

If the record is not present on T1, nothing is returned, regardless of the fact that the row exists on T2, T3, T4 or T5.

1) Does anyone actually understand what I've just waffled on about? ;-)

2) Can anyone help? I'm pulling my hair out!

Thank you in advance.

 

[PruSQLer]

Try a full outer join. That should bring everything that's out there back.

 

[peskyangel]

Yes, I've tried that as well. It brings everything back as long as there is a row on T1.

If there isn't a row on T1, nothing is returned, regardless of the fact that rows exist on T2, T3 etc.

 

[PruSQLer]

Are you using OS 390? That may be a little different when it comes to full outer and right outer joins.

 

[kkitt]

What are you expecting back for the T1 variables?

Also are you wanting to join T1 to the "BASSDEV.BRC_EXCEPTION" table twice as t4 and t5?

This may not cure your problem, but will speed up the query by elimanting a join not needed.

 

[PruSQLer]

Good catch by kkitt. Looks like you made a copy and paste error.

Also, is the following clause the source of your limited output?

WHERE (T1.SEGMENT = '01'
AND T1.CORP = '10'
AND T1.ACCOUNT = '4775965306368373')


That would only give you rows when it fits the criteria you specified for T1.

 

[kkitt]

This does not answer your question, but does bring up another point that needs to though about.


Has there been thought as to how to handle the joins if the data is on T1 and not T2. The join would then have to be switched to a "LEFT OUTER JOIN".

Definitions:

RIGHT OUTER - Include all records from T2 and only those on T1 where join is equal.

This means that if the data is on T1 and not on T2 then do not include it in the result set.


These joins could get nasty if the data is on t1, t2, t4 and not on t3, t5 with having to mix both "RIGHT OUTER JOIN" and "LEFT OUTER JOIN".

Is there one table that the data is ALWAYS ON (driver), if so then join all the table to that one with "LEFT OUTER JOINS" and that should solve the issue with data missing off any of the others except the MAIN Table.

 

[peskyangel]

Thanks to kkitt and PruSQLer. T5 is actually meant to be a different table, BASSDEV.BRC_APP_FRAUD.
I don't think there's a "driving" table as such, i.e. one table on which there will always be a row.
At the moment, I'm not sure if it's just as easy to code 5 individual calls.
Thanks for the advice, though.

 

[kkitt]

5 separate queries is going to have to be it, I do not think that you will be able to come up with a single query that joins all five tables and get the results that you want unless there is a master table.

I did a sample with just two table (T1 and T2) with the data missing in T1 and got the query to work with the RIGHT OUTER JOIN. I then added the data to T1 and removed it from T2 and got nothing as the result, unless I changed to a LEFT OUTER JOIN. Now add all the other combinations and we get a mess.

 

[peskyangel]

That's the conclusion I'm coming to as well.

Thanks for all your help, though!

 

[PruSQLer]

I'm sorry but I can't let this die. I did the following on some test tables on my database and it brought back all relevant rows from all tables with nulls in the appropriate places. That's what a full outer join does.

select distinct t1.agmt_pkge_id, t2.agmt_pkge_id, t3.agmt_pkge_id, t4.agmt_pkge_id
from pfmc.rwt_agmt_elem_prod t1 full outer join pfmc.rwt_agmt_pkge t2
on t1.agmt_pkge_id = t2.agmt_pkge_id

Full outer join pfmc.rwt_mon_prv_single t3
on t1.agmt_pkge_id = t3.agmt_pkge_id

full outer join pfmc.rwt_pips_lgr_trans t4
on t1.agmt_pkge_id = t4.agmt_pkge_id
order by t1.agmt_pkge_id

The only way this wouldn't work is if there are platform constraints (e.g. OS 390) or the WHERE clause is applying "pre join" limitations (IMHO)

 

[peskyangel]

Thanks for not letting it die!

I think the problem I'm having is with the WHERE clause.
Logically, it sounds so easy. I have 5 tables, all with the same key. I want to read those 5 tables, with the same key information, and return specific columns from them, as a one row result set. The join you mention above returns information, but not for one specific row. That's the problem I've got!

 

[PruSQLer]

So you're always going to use specific criteria involving segment, corp and account on table1 when you run this? And you want any matching rows from the other tables included?

What platform are you using for your DB2 (v7?) ?

 

[peskyangel]

Yep. SEGMENT, CORP and ACCOUNT is always known.

The full outer join works unless there's no row on the table specified in the WHERE clause. We're using version 7.1.

 

[lespaul]

can't you take this query that returns all the information you need in the format you need (at least that's the impression I got) and create a temp table or keyed logical file and then run your final query with the criteria against this information?

Leslie

 

[peskyangel]

I could do that, I suppose, but would that provide me with any performance gain?
Am I missing something fundamental, do you think?
The pseudo SQL query I'd like to run is this:

SELECT T1.STATUS_TYP, T1.STATUS_START_TS
,T2.STATUS_TYP
,T3.STATUS_TYP, T3.STATUS_START_TS
,T4.STATUS_TYP, T4.STATUS_START_TS
,T5.STATUS_TYP, T5.STATUS_START_TS
FROM BASSDEV.BRC_DECEASED T1
,BASSDEV.BRC_DEFAULT T2
,BASSDEV.BRC_GONE_AWAY T3
,BASSDEV.BRC_EXCEPTION T4
,BASSDEV.BRC_APP_FRAUD T5
WHERE all tables SEGMENT = '01'
and all tables CORP = '10'
and all tables ACCOUNT = '4775965306368373'

IF SQLCODE = -100 on any table, still read from the other table and return any matching information.

Return 1 row.

 

[peskyangel]

So, if the row is on none of the tables, I'll get a -100. That's fine.

If the row is on T1, say, and not the others, then I'll have one row, where T1.STATUS_TYP and T1.STATUS_START_TS are populate, but the T2, T3, T4 and T5 fields are null.

If the row is on T2, T4 and T5, then again, I'll have one row. This time, T1 fields are null, T2 populated, T3 null, T4 and T5 populated.

 

[PruSQLer]

Ahhhh, I finally see what you're trying to do. Yeah, you can't do that with a fuller outer join.

But you can do it in db2 with the great Table Expressions feature, or temp tables feature which we call them. It's pretty easy. (maybe you already use these) You'll have to adjust to your needs but the following worked in my test database:

with t1 (id_1, id_2)
As (
select distinct t1.agmt_pkge_id, t2.agmt_pkge_id
from pfmc.rwt_agmt_elem_prod t1 left outer join pfmc.rwt_agmt_pkge t2
on t1.agmt_pkge_id = t2.agmt_pkge_id
Where t1.agmt_pkge_id = ' 628838040000')
,
t2 (id_1, id_2, id_3)
As (
Select distinct id_1, id_2, t3.agmt_pkge_id
From t1 left outer join pfmc.rwt_mon_prv_single t3
on id_1 = t3.agmt_pkge_id )

Select distinct id_1, id_2, id_3, t4.agmt_pkge_id

from t2 left outer join pfmc.rwt_pips_lgr_trans t4
on id_1 = t4.agmt_pkge_id

I don't know if you need the 'distinct' in there, probably not. Hope this works for you.

 

[lespaul]

I guess I was on the right track!!!



Leslie

 

[peskyangel]

I'm glad you understand what I'm waffling on about, but I can't get the SQL you've posted to work.
Is it correct to start the SQL with WITH?

The reasoning behind making the change is that I thought one join of all 5 tables would be quicker than 5 individual calls. This is an online system of around 400,000 transactions in a working day. The current program in production (without the 5 calls) uses about 15 GETPAGES per transaction. The program in UAT, with the 5 individual calls uses roughly 50 GETPAGES.
I guess the creation of a temp. table would add more overhead. Perhaps I should leave well alone.

Thanks again to everyone that's contributed to this very interesting thread.