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

    Unanswered: Select query should return one column as twice

    Hi

    Please find the 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.tran_des as tran_des2
    
    from
    
      table1 t1,
      table2 t2,
      table3 t3
    where
    
         t1.p_LOC=t2.tran_no
         t2.tran_code=t3.tran_item
    TABLE1

    Code:
    p_loc   p_NOR        p_det
    A11     1234         aaa
    B11     1311         WWW
    TABLE2

    Code:
    tran_no       tran_code
    A11              A11
    B11              S11
    TABLE3
    Code:
    tran_ITEM      tran_des
    A11              sout
    B11              west
    S11              EAST
    I need the select query should return result set like below..If the TRAN_NO and TRAN_CODE has same value in the tables means that particular matching TRAN_DES value should display in both columns(TRAN_DES1,TRAN_DES2)

    If not having same value means,the equivalent value of TRAN_No should display in TRAN_DES1 column and equivalent value of TRAN_CODE should display in TRAN_DES2 column value

    Expected Resultset
    Code:
    p_LOC         p_nort       p-det   tran_no   tran-code   tran_des1   tran-des2 
    A11           1234          aaa       A11      A11          SOUT       SOUT
    B11           1311          WWW       B11      S11          west       EAST
    Pl help...

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    An example using left outer join with on condition t2.tran_no <> t2.tran_code.

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     TABLE1(p_loc , p_nor , p_det) AS (
    VALUES
      ( 'A11' , 1234 , 'aaa' )
    , ( 'B11' , 1311 , 'WWW' )
    )
    , TABLE2(tran_no , tran_code) AS (
    VALUES
      ( 'A11' , 'A11' )
    , ( 'B11' , '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
     INNER JOIN
           table2 t2
       ON  t2  .tran_no   =  t1.p_loc
     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
    ;
    ------------------------------------------------------------------------------
    
    P_LOC P_NOR       P_DET TRAN_NO TRAN_CODE TRAN_DES1 TRAN_DES2
    ----- ----------- ----- ------- --------- --------- ---------
    A11          1234 aaa   A11     A11       sout      sout     
    B11          1311 WWW   B11     S11       west      EAST     
    
      2 record(s) selected.
    A scalar-subselect would work, too.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 1 is a good example to demonstrait the rule...
    you should not mix traditional join syntax and modern join syntax.

    If you changed INNER JOINs in Example 1 to traditional joins, you would get error, like the following example.

    Example 1x: SQL0338N error.
    Code:
    ...
     FROM
           table1 t1
         , table2 t2
         , table3 t3
     LEFT  OUTER JOIN
           table3 t3_2
       ON  t2  .tran_no   <> t2.tran_code
       AND t3_2.tran_item =  t2.tran_code
     WHERE t2  .tran_no   =  t1.p_loc
       AND t3  .tran_item =  t2.tran_no
    ;

  4. #4
    Join Date
    Sep 2011
    Posts
    220
    superb..tonkuma...thanks a lot..working fine

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The basic rule of outer join which I want to notice repeatedly is...

    the ON condition of outer join determines
    whather the row(s) of inner table(right table in left outer join, left table in right outer join) be joined or not,

    the rows of outer table are always selected,
    even if the ON condition includes only the columns of outer table, and/or includes no column of inner table.
    Last edited by tonkuma; 12-09-11 at 21:17. Reason: Correct English grammer.

  6. #6
    Join Date
    Sep 2011
    Posts
    220
    Hi

    For the above same queryin example1. i was joining one more table use of inner join and using some filter conditon
    in where clause like below
    Code:
    WHERE
    
        (T1.P_nor   BETWEEN :WS-ALL-nor-LOW    
                              AND  :WS-ALL-nor-HIGH)  
    AND T1.txt_TYP      IN ( 'D','I','U' )      
    AND DATE (T1.UPD_TIME)                       
                             BETWEEN :WS-BGNT-DT
                               AND  :WS-FIN-DT   
    AND                                                  
         T1.P_LOC    IN  (:P1,:P2,:P3........,:P75)  -->means up to P75
    AND                                   
        (                                 
        (:WS-ALL-DEALS  =  '#')  OR 
        (T4.DEAL_CODE    IN    (:D1,:D2,:D3....,:D15)  -->means up to D15
    We are passing the # value to WS-ALL-nor-LOW,WS-ALL-nor-high to fetch all the
    combination of P_nor rows

    Passing literal value '0001-01-01' to WS-BGNT-DT
    '9999-12-31' to WS-FIN-DT

    p_loc has 75 numbers code such as p1,p2,p3,p4...up to p75

    We are passing the # value to WS-ALL-DEALS to fetch all the
    combination of DEAL_CODE rows OR passing the particular DEAL_CODE value.
    DEAL_CODE has 10 number of code like D1,D2.D3...D10

    while i am executing the proc, facing some performance issue and closed as TIME out error

    Please tell me., How to change the query to avoid the Performance issue?

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why didn't you write whole query?
    Without seeing whole query,
    I need to guess something and it may be different from your query.

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

    Please find the query
    Code:
    SELECT t1.p_loc
         , t1.p_nor
         , t1.p_det
         , T4.DEAL_CODE
         , 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
    
     INNER JOIN
           TABLE4 T4
    
     ON    T2.P_LOC=T4.P_LOC
     INNER JOIN
           table2 t2
       ON  t2  .tran_no   =  t1.p_loc
     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
    
    WHERE
    
        (T1.P_nor   BETWEEN :WS-ALL-nor-LOW    
                              AND  :WS-ALL-nor-HIGH)  
    AND T1.txt_TYP      IN ( 'D','I','U' )      
    AND DATE (T1.UPD_TIME)                       
                             BETWEEN :WS-BGNT-DT
                               AND  :WS-FIN-DT   
    AND                                                  
         T1.P_LOC    IN  (:P1,:P2,:P3........,:P75)  -->means up to P75
    AND                                   
        (                                 
        (:WS-ALL-DEALS  =  '#')  OR 
        (T4.DEAL_CODE    IN    (:D1,:D2,:D3....,:D15)  -->means up to D15

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1) There are some syntax error and inconsistency.

    You might think that it is only a careless mistake and/or they are not related strongly to your issue.
    But, DB2 don't guess for you, don't accept them.
    DB2 may simply return error message and stop further processing of your SQL statement.
    And I sympathize with DB2(or such rigorous computer software systems)
    rather than sloppy inaccurate work of human being.

    1-1) Incorrect qualifier.
    Marked bold and red color.
    Code:
           table1 t1
    
     INNER JOIN
           TABLE4 T4
    
     ON    T2.P_LOC=T4.P_LOC
    1-2) Un-matched parentheses.
    Code:
    AND                                   
        (                                 
        (:WS-ALL-DEALS  =  '#')  OR 
        (T4.DEAL_CODE    IN    (:D1,:D2,:D3....,:D15)  -->means up to D15
    1-3) There is no UPD_TIME column in table1 in your OP.


    (2) Did you created any index on table4?
    If so, please write the create index statements.

Posting Permissions

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