| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
08-10-11, 14:49
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 3
|
|
Chasing thru Hierarchy of tables when value is null
|
|
Hi, Hoping some one can help me figure out some DB2 SQL that will allow me to chase up a hierarchy of tables for a value when the lower level values are null ie:
Table A Table B Table C
empl name null null Smith
Also,
There may be instances based on other fields in Table A where I would want to skip looking for the field in Table B if Table A value is null, and go straight to Table C for the value
|
|
08-10-11, 15:12
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Please give me some sample data and required result including normal(for example, non-null values) and exceptional(some null values) rows.
I hope at least 5 to 10 rows of sample data.
(Too little sample may result shortcut SQL query which may not applicable to real data.)
|
|
08-10-11, 15:27
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 3
|
|
|
|
Sample data:
Tables
MER_T1 PCH_T1 PEN_T1
pgm-num chn-num ent-num chn-num ent-num pgm-num ent-num pgm-num
null 22 55 22 55 1 55 1
null 0 55
2 0 55
In the above example
the first line with the MER_T1.pgm-num = NULL, and MER_T1.chn-num <> 0, I would go to PCH.T1 for pgm-num
the 2nd line with the MER_T1.pgm-num being null but MER_T1.chn-num = 0, I would go to PEN_T1 for pgm-num
the 3rd line with MER_T1.pgm-num not NULL, I would use the value from MER_T1.pgm-num
|
|
08-10-11, 15:44
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Entirery insufficient for me.
I wrote
Quote:
I hope at least 5 to 10 rows of sample data.
(Too little sample may result shortcut SQL query which may not applicable to real data.)
|
Please seperate each tables and supply more sample data and expected result.
Or, you can expect help from other persons than me.
For example:
MER_T1
pgm-num chn-num ent-num
null 22 55
null 0 55
2 0 55
PCH_T1
chn-num ent-num pgm-num
22 55 1
PEN_T1
ent-num pgm-num
55 1
|
|
08-10-11, 16:05
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 3
|
|
MER_T1
MER-NUM CHN-NUM ENT-NUM PGM-NUM
1234 0 22 1
5555 11 23 NULL
7575 12 22 NULL
8888 0 22 1
6767 15 25 3
7777 17 22 NULL
7878 0 25 NULL
PCH_T1
CHN-NUM ENT-NUM PGM-NUM
11 23 2
12 22 NULL
15 25 3
17 22 NULL
PEN_T1
ENT-NUM PGM-NUM
22 1
23 2
25 3
SELECT MER-NUM, PGM-NUM FROM ABOVE TABLES, STARTING AT MER_T1, THEN PCH_T1, THEN PEN_T1, UNTIL PGM NOT NULL
EXPECTED RESULTS:
MER-NUM PGM-NUM
1234 1 (SOURCE - MER_T1)
5555 2 (SOURCE - PCH_T1)
7575 1 (SOURCE - PEN_T1)
8888 1 (SOURCE - MER_T1)
6767 3 (SOURCE - MER_T1)
7777 1 (SOURCE - PEN_T1)
7878 3 (SOURCE - PEN_T1 because MER_T1.chn-num = 0)
|
|
08-10-11, 19:55
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
This would give you the expected result.
But, it joined some unnecessary rows.
(I changed the values of mer_num to make sequence of output rows same as mer_t1.)
Example 1:
Code:
------------------------------ Commands Entered ------------------------------
WITH
MER_T1(MER_NUM , CHN_NUM , ENT_NUM , PGM_NUM) AS (
VALUES
( 1234 , 0 , 22 , 1 )
, ( 2555 , 11 , 23 , NULLIF(0 , 0) )
, ( 3575 , 12 , 22 , NULLIF(0 , 0) )
, ( 4888 , 0 , 22 , 1 )
, ( 6767 , 15 , 25 , 3 )
, ( 7777 , 17 , 22 , NULLIF(0 , 0) )
, ( 8878 , 0 , 25 , NULLIF(0 , 0) )
)
, PCH_T1(CHN_NUM , ENT_NUM , PGM_NUM) AS (
VALUES
( 11 , 23 , 2 )
, ( 12 , 22 , NULLIF(0 , 0) )
, ( 15 , 25 , 3 )
, ( 17 , 22 , NULLIF(0 , 0) )
)
, PEN_T1(ENT_NUM , PGM_NUM) AS (
VALUES
( 22 , 1 )
, ( 23 , 2 )
, ( 25 , 3 )
)
SELECT mer_num
, COALESCE(m.pgm_num , c.pgm_num , e.pgm_num) AS ner_num
, m.pgm_num AS mer_pgm_num
, c.pgm_num AS pch_pgm_num
, e.pgm_num AS pen_pgm_num
FROM mer_t1 m
LEFT OUTER JOIN
pch_t1 c
ON c.chn_num = m.chn_num
LEFT OUTER JOIN
pen_t1 e
ON e.ent_num = m.ent_num
ORDER BY
mer_num
;
------------------------------------------------------------------------------
MER_NUM NER_NUM MER_PGM_NUM PCH_PGM_NUM PEN_PGM_NUM
----------- ----------- ----------- ----------- -----------
1234 1 1 - 1
2555 2 - 2 2
3575 1 - - 1
4888 1 1 - 1
6767 3 3 3 3
7777 1 - - 1
8878 3 - - 3
7 record(s) selected.
This wouldn't joine unnecessary rows.
Example 2:
Code:
SELECT mer_num
, COALESCE(m.pgm_num , c.pgm_num , e.pgm_num) AS ner_num
, m.pgm_num AS mer_pgm_num
, c.pgm_num AS pch_pgm_num
, e.pgm_num AS pen_pgm_num
FROM mer_t1 m
LEFT OUTER JOIN
pch_t1 c
ON c.chn_num = m.chn_num
AND m.pgm_num IS NULL
LEFT OUTER JOIN
pen_t1 e
ON e.ent_num = m.ent_num
AND COALESCE(m.pgm_num , c.pgm_num) IS NULL
ORDER BY
mer_num
;
------------------------------------------------------------------------------
MER_NUM NER_NUM MER_PGM_NUM PCH_PGM_NUM PEN_PGM_NUM
----------- ----------- ----------- ----------- -----------
1234 1 1 - -
2555 2 - 2 -
3575 1 - - 1
4888 1 1 - -
6767 3 3 - -
7777 1 - - 1
8878 3 - - 3
7 record(s) selected.
|
|
08-10-11, 20:08
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Example 2 still joined unnecessary rows
Example 2a:
Code:
SELECT mer_num
, COALESCE(m.pgm_num , c.pgm_num , e.pgm_num) AS mer_num
, m.pgm_num AS mer_pgm_num
, c.chn_num AS pch_chn_num
, e.ent_num AS pen_ent_num
FROM mer_t1 m
LEFT OUTER JOIN
pch_t1 c
ON c.chn_num = m.chn_num
AND m.pgm_num IS NULL
LEFT OUTER JOIN
pen_t1 e
ON e.ent_num = m.ent_num
AND m.pgm_num IS NULL
AND c.pgm_num IS NULL
/* equivalent to
AND COALESCE(m.pgm_num , c.pgm_num) IS NULL
*/
ORDER BY
mer_num
;
------------------------------------------------------------------------------
MER_NUM MER_NUM MER_PGM_NUM PCH_CHN_NUM PEN_ENT_NUM
----------- ----------- ----------- ----------- -----------
1234 1 1 - -
2555 2 - 11 -
3575 1 - 12 22
4888 1 1 - -
6767 3 3 - -
7777 1 - 17 22
8878 3 - - 25
7 record(s) selected.
This would join no extra rows.
Example 3:
Code:
SELECT mer_num
, COALESCE(m.pgm_num , c.pgm_num , e.pgm_num) AS mer_num
, m.pgm_num AS mer_pgm_num
, c.chn_num AS pch_chn_num
, e.ent_num AS pen_ent_num
FROM mer_t1 m
LEFT OUTER JOIN
pch_t1 c
ON c.chn_num = m.chn_num
AND m.pgm_num IS NULL
AND c.pgm_num IS NOT NULL
LEFT OUTER JOIN
pen_t1 e
ON e.ent_num = m.ent_num
AND m.pgm_num IS NULL
AND c.pgm_num IS NULL
/* equivalent to
AND COALESCE(m.pgm_num , c.pgm_num) IS NULL
*/
ORDER BY
mer_num
;
------------------------------------------------------------------------------
MER_NUM MER_NUM MER_PGM_NUM PCH_CHN_NUM PEN_ENT_NUM
----------- ----------- ----------- ----------- -----------
1234 1 1 - -
2555 2 - 11 -
3575 1 - - 22
4888 1 1 - -
6767 3 3 - -
7777 1 - - 22
8878 3 - - 25
7 record(s) selected.
|
|
08-10-11, 21:30
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Sample data should include enough data(including boundary and/or exceptional case)
to remove ambiguity and to be valid for debugging as much as possible.
From your data, PCH_T1 is not neccesary to get the required result.
Example x1:
Code:
------------------------------ Commands Entered ------------------------------
WITH
MER_T1(MER_NUM , CHN_NUM , ENT_NUM , PGM_NUM) AS (
VALUES
( 1234 , 0 , 22 , 1 )
, ( 2555 , 11 , 23 , NULLIF(0 , 0) )
, ( 3575 , 12 , 22 , NULLIF(0 , 0) )
, ( 4888 , 0 , 22 , 1 )
, ( 6767 , 15 , 25 , 3 )
, ( 7777 , 17 , 22 , NULLIF(0 , 0) )
, ( 8878 , 0 , 25 , NULLIF(0 , 0) )
)
, PEN_T1(ENT_NUM , PGM_NUM) AS (
VALUES
( 22 , 1 )
, ( 23 , 2 )
, ( 25 , 3 )
)
SELECT mer_num
, COALESCE(m.pgm_num /*, c.pgm_num*/ , e.pgm_num) AS mer_num
FROM mer_t1 m
/*
LEFT OUTER JOIN
pch_t1 c
ON c.chn_num = m.chn_num
AND m.pgm_num IS NULL
AND c.pgm_num IS NOT NULL
*/
LEFT OUTER JOIN
pen_t1 e
ON e.ent_num = m.ent_num
-- AND COALESCE(m.pgm_num , c.pgm_num) IS NULL
AND m.pgm_num IS NULL
-- AND c.pgm_num IS NULL
ORDER BY
mer_num
;
------------------------------------------------------------------------------
MER_NUM MER_NUM
----------- -----------
1234 1
2555 2
3575 1
4888 1
6767 3
7777 1
8878 3
7 record(s) selected.
Even PEN_T1 is not neccesary.
This may be too stupid.
But, if you saw only input and output, it might be OK.
Example x2:
Code:
------------------------------ Commands Entered ------------------------------
WITH
MER_T1(MER_NUM , CHN_NUM , ENT_NUM , PGM_NUM) AS (
VALUES
( 1234 , 0 , 22 , 1 )
, ( 2555 , 11 , 23 , NULLIF(0 , 0) )
, ( 3575 , 12 , 22 , NULLIF(0 , 0) )
, ( 4888 , 0 , 22 , 1 )
, ( 6767 , 15 , 25 , 3 )
, ( 7777 , 17 , 22 , NULLIF(0 , 0) )
, ( 8878 , 0 , 25 , NULLIF(0 , 0) )
)
SELECT mer_num
, COALESCE(pgm_num , 3 - MOD(INT( ROW_NUMBER() OVER() - 1) , 3) )AS mer_num
FROM mer_t1
;
------------------------------------------------------------------------------
MER_NUM MER_NUM
----------- -----------
1234 1
2555 2
3575 1
4888 1
6767 3
7777 1
8878 3
7 record(s) selected.
|
Last edited by tonkuma; 08-10-11 at 21:50.
Reason: Remove unnecessary test data(table). Replace Example x1 with right one.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Chasing thru Hierarchy of tables when value is null
