Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2011
    Posts
    3

    Question Unanswered: 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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.)

  3. #3
    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

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Entirery insufficient for me.

    I wrote
    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

  5. #5
    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)

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 22:50. Reason: Remove unnecessary test data(table). Replace Example x1 with right one.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •