Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: Select query should return Null column row

    Hi;

    For below query, we have to handle the NULL rows also

    Code:
    SELECT t1.p_loc
         , t1.p_nor
         , t1.p_det
         , t2.tran_no
         , t2.tran_code
         , t3.tran_des AS tran_des1
         , COALESCE(
              t3_2.tran_des
            , t3  .tran_des
           ) AS tran_des2
     FROM
           table1 t1
     left outer JOIN
           table2 t2
       ON  t2  .tran_no   =  t1.p_loc
    and    t2.p_nor= t1.p_nor
     INNER JOIN
           table3 t3
       ON  t3  .tran_item =  t2.tran_no
     LEFT  OUTER JOIN
           table3 t3_2
       ON  t2  .tran_no   <> t2.tran_code
       AND t3_2.tran_item =  t2.tran_code
    Table1
    Code:
    p_loc   p_NOR        p_det
    A11     1234         aaa
    A11     3334         aaa
    B11     1311         WWW
    Table2
    Code:
    tran_no  p_nor   tran_code
    A11      1234    A11
    A11      3334    
    B11      1311    S11
    Table3
    Code:
    tran_ITEM      tran_des
    A11              sout
    B11              west
    S11              EAST
    Expected Result set
    Code:
    p_LOC         p_nor        p-det   tran_no   tran-code   tran_des1   tran-des2 
    A11           1234          aaa       A11      A11          SOUT       SOUT
    A11           3334          aaa       A11                   SOUT       
    B11           1311          WWW       B11      S11          west       EAST
    Please help.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Would you supply sample/test data of table1, table2 and table3 with one of the following ways, if you want my response.

    (1) CREATE TABLE statements and INSERT statements.
    or
    (2) WITH common-table-expressions.

    You are free to answer my request, or not.

  3. #3
    Join Date
    Sep 2011
    Posts
    220
    HI;

    Please find the query with CTE
    Code:
    WITH
     TABLE1(p_loc , p_nor , p_det) AS (
    VALUES
      ( 'A11' , 1234 , 'aaa' )
    , ( 'A11' , 3334 , 'aaa' )
    , ( 'B11' , 1311 , 'WWW' )
    )
    , TABLE2(tran_no ,p_nor , tran_code) AS (
    VALUES
      ( 'A11' ,1234, 'A11' )
    , ( 'A11' ,3334, '   ' )
    , ( 'B11' ,1311, 'S11' )
    )
    , TABLE3(tran_item , tran_des) AS (
    VALUES
      ( 'A11' , 'sout' )
    , ( 'B11' , 'west' )
    , ( 'S11' , 'EAST' )
    )
    SELECT t1.p_loc
         , t1.p_nor
         , t1.p_det
         , t2.tran_no
         , t2.tran_code
         , t3.tran_des AS tran_des1
         , COALESCE(
              t3_2.tran_des
            , t3  .tran_des
           ) AS tran_des2
     FROM
           table1 t1
     left outer JOIN
           table2 t2
       ON  t2  .tran_no   =  t1.p_loc
    and    t2.p_nor= t1.p_nor
     INNER JOIN
           table3 t3
       ON  t3  .tran_item =  t2.tran_no
     LEFT  OUTER JOIN
           table3 t3_2
       ON  t2  .tran_no   <> t2.tran_code
       AND t3_2.tran_item =  t2.tran_code

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about simple LEFT OUTER JOIN?

    Code:
    SELECT t1.p_loc
         , t1.p_nor
         , t1.p_det
         , t2.tran_no
         , t2.tran_code
         , t3.tran_des   AS tran_des1
         , t3_2.tran_des AS tran_des2
     FROM
           table1 t1
     left outer JOIN
           table2 t2
       ON  t2  .tran_no   =  t1.p_loc
    and    t2.p_nor= t1.p_nor
     INNER JOIN
           table3 t3
       ON  t3  .tran_item =  t2.tran_no
     LEFT  OUTER JOIN
           table3 t3_2
       ON  t3_2.tran_item =  t2.tran_code
     ORDER BY
           p_loc
         , p_nor
    ;
    ------------------------------------------------------------------------------
    
    P_LOC P_NOR       P_DET TRAN_NO TRAN_CODE TRAN_DES1 TRAN_DES2
    ----- ----------- ----- ------- --------- --------- ---------
    A11          1234 aaa   A11     A11       sout      sout     
    A11          3334 aaa   A11               sout      -        
    B11          1311 WWW   B11     S11       west      EAST     
    
      3 record(s) selected.

  5. #5
    Join Date
    Sep 2011
    Posts
    220
    Hi;

    I was using the below query
    Code:
    SELECT t1.p_loc
         , t1.p_nor
         , t1.p_det
         , t2.tran_no
         , t2.tran_code
         , t3.tran_des   AS tran_des1
         , t3_2.tran_des AS tran_des2
     FROM
           table1 t1
     left outer JOIN
           table2 t2
       ON  t2  .tran_no   =  t1.p_loc
    and    t2.p_nor= t1.p_nor
     INNER JOIN
           table3 t3
       ON  t3  .tran_item =  t2.tran_no
     LEFT  OUTER JOIN
           table3 t3_2
       ON  t3_2.tran_item =  t2.tran_code
     ORDER BY
           p_loc
         , p_nor
    ;
    But it returns ' no rows found'

    so that i have added LEFT OUTER JOIN instead of INNER join on below part

    Code:
    and    t2.p_nor= t1.p_nor
     /* INNER JOIN  */
    LEFT OUTER JOIN
           table3 t3
       ON  t3  .tran_item =  t2.tran_no
    it retuns result set like below
    Code:
    p_LOC         p_nor        p-det   tran_no   tran-code   tran_des1   tran-des2 
    A11           1234          aaa       A11      A11          SOUT       SOUT
    A11           3334          aaa                             
    B11           1311          WWW       B11      S11          west       EAST
    Pl help

  6. #6
    Join Date
    Sep 2011
    Posts
    220
    Working fine... Please ignore the above post...thanks

Posting Permissions

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