Select query should return one column as twice

Billa007 · #1 (**permalink**) 12-09-11, 06:39

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

tonkuma · #2 (**permalink**) 12-09-11, 07:23

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.

tonkuma · #3 (**permalink**) 12-09-11, 07:38

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
;

Billa007 · #4 (**permalink**) 12-09-11, 10:02

superb..tonkuma...thanks a lot..working fine

tonkuma · #5 (**permalink**) 12-09-11, 10:37

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.

Billa007 · #6 (**permalink**) 12-12-11, 07:40

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?

tonkuma · #7 (**permalink**) 12-12-11, 21:14

Why didn't you write whole query?
Without seeing whole query,
I need to guess something and it may be different from your query.

Billa007 · #8 (**permalink**) 12-13-11, 07:53

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

tonkuma · #9 (**permalink**) 12-13-11, 08:53

(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.

Internet Services

Web Development

Digital Marketing

Consumer Tech