HOW CAN i write this in single query?

laknar · #1 (**permalink**) 04-06-09, 05:24

Code:

CREATE TABLE TABLE1(COLUMN1 VARCHAR(20),COLUMN2 VARCHAR(20),COLUMN3 VARCHAR(20))

CREATE TABLE TABLE2(FUN1 VARCHAR(20),FUN2 VARCHAR(20),FUN3 VARCHAR(20))


CREATE TABLE TABLE3(FILE1 VARCHAR(20),FILE2 VARCHAR(20),FILE3 VARCHAR(20))


INSERT INTO TABLE1 VALUES('A','W','SEAT');
INSERT INTO TABLE1 VALUES('B','S','SEAT1');
INSERT INTO TABLE1 VALUES('C','S','SEAT1');
INSERT INTO TABLE1 VALUES('D','S','SEAT1');

INSERT INTO TABLE2 VALUES('A','B','PRD');
INSERT INTO TABLE2 VALUES('A','C','ABC');
INSERT INTO TABLE2 VALUES('A','D','TYP');


INSERT INTO TABLE3 VALUES('S','20050101','PRD');
INSERT INTO TABLE3 VALUES('S','20050102','ABC');
INSERT INTO TABLE3 VALUES('S','20050103','TYP');

By joining all the above three tables (select COLUMN2,FUN2,FIELD1 FROm TABLE1 WHERE COLUMN='A' ) all the above three tables.

By matching COLUMN1(TABLE1) WITH FUN1 Column in Table2 and FUN3(TABLE2) With FIELD3(TABLE3)

FOR 'A' IN COLUMN1(TABLE1) by joining with TABLE2(FUN1) then take FUN2 VAlues and JOIN WITH COLUMN1(TABLE1) RETRIEVE COLUMN2 Values for the corresponding FUN2 Values.

HOW CAN i write this in single query?

RESULT SHOULD BE THE BELOW

COLUMN1 COLUMN2 FIELD1
B S S
C S S
D S S

tonkuma · #2 (**permalink**) 04-06-09, 08:41

I felt that your description had contradictions and inconsistencies.
And sample data are not so good to show your requirements.
Because, all result values of column2 and field1 are same('S'). It gives no significant information for me.

I used full of my imaginations. So, I may misunderstand your requirement.

Code:

------------------------------ Commands Entered ------------------------------
SELECT t1b.column1
     , t1b.column2
     , t3.field1
  FROM
       table1 t1a
  JOIN
       table2 t2
   ON  t2.fun1 = t1a.column1
  JOIN
       table3 t3
   ON  t3.field3 = t2.fun3
  JOIN
       table1 t1b
   ON  t1b.column1 = t2.fun2
 WHERE t1a.column1 = 'A'
;
------------------------------------------------------------------------------

COLUMN1              COLUMN2              FIELD1              
-------------------- -------------------- --------------------
B                    S                    S                   
C                    S                    S                   
D                    S                    S                   

  3 record(s) selected.

laknar · #3 (**permalink**) 04-07-09, 06:08

When running this query in DB it is retreiving results.
but when i embed the same query with Stored Procedure all the values are getting as NUll.

The value 'A' is passing as parameter in SP .

i have created the temp table to check how the values are passing.
The values are passing perfectly.
how to eliminate the null values.

tonkuma · #4 (**permalink**) 04-07-09, 06:46

I want to see your code in the Stored Procedure.

I have some questions.
How did you received the results?
How did you used the passed value in your select statement?
etc.

laknar · #5 (**permalink**) 04-07-09, 06:57

thank you tonkuma,it was the mistake of mine.
got the result as expected.

tonkuma · #6 (**permalink**) 04-07-09, 09:43

Your code worked well on my environment.

1) query data and make templak table.

Code:

------------------------------ Commands Entered ------------------------------
connect to SAMPLE ;
------------------------------------------------------------------------------

   Database Connection Information

 Database server        = DB2/NT 9.5.2
 SQL authorization ID   = DB2ADMIN
 Local database alias   = SAMPLE


A JDBC connection to the target has succeeded.
----------------------------- Commands Entered ------------------------------
SELECT * FROM table1;
------------------------------------------------------------------------------

COLUMN1              COLUMN2              COLUMN3             
-------------------- -------------------- --------------------
A                    W                    SEAT                
B                    S                    SEAT1               
C                    S                    SEAT1               
D                    S                    SEAT1               

  4 record(s) selected.


------------------------------ Commands Entered ------------------------------
SELECT * FROM table2;
------------------------------------------------------------------------------

FUN1                 FUN2                 FUN3                
-------------------- -------------------- --------------------
A                    B                    PRD                 
A                    C                    ABC                 
A                    D                    TYP                 

  3 record(s) selected.


------------------------------ Commands Entered ------------------------------
SELECT * FROM table3;
------------------------------------------------------------------------------

FIELD1               FIELD2               FIELD3              
-------------------- -------------------- --------------------
S                    20050101             PRD                 
S                    20050102             ABC                 
S                    20050103             TYP                 

  3 record(s) selected.


------------------------------ Commands Entered ------------------------------
CREATE TABLE templak
(column1 VARCHAR(20)
);
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

2) execute update.

Code:

------------------------------ Commands Entered ------------------------------
BEGIN ATOMIC

DECLARE v_column1 VARCHAR(20);

FOR DEPPROCESS AS
  Select COLUMN1 from TABLE1 WHERE COLUMN2 = 'W'
DO
  SET v_column1 = COLUMN1;
  UPDATE TABLE1
     SET COLUMN2 = 'S'
   WHERE NOT EXISTS
       ( select *
           from
              ( SELECT t1b.column1
                     , t1b.column2 as S1
                     , t3.field1   AS S2
                  FROM
                       table1 t1a
                  JOIN
                       table2 t2
                    ON t2.fun1 = t1a.column1
                  JOIN
                       table3 t3
                    ON t3.field3 = t2.fun3
                  JOIN
                       table1 t1b
                    ON t1b.column1 = t2.fun2
                 WHERE t1a.column1 = v_column1
              ) AB
          WHERE AB.s1 <> 'S'
             OR AB.s2 <> 'S'
       )
     AND COLUMN1 = v_column1
  ;
  INSERT INTO templak VALUES(v_column1);

END FOR;
END@
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

3) check results.
column2 of row 1(column1 = 'A') was updated to 'S'.

Code:

------------------------------ Commands Entered ------------------------------
SELECT * FROM table1@
------------------------------------------------------------------------------

COLUMN1              COLUMN2              COLUMN3             
-------------------- -------------------- --------------------
A                    S                    SEAT                
B                    S                    SEAT1               
C                    S                    SEAT1               
D                    S                    SEAT1               

  4 record(s) selected.


------------------------------ Commands Entered ------------------------------
SELECT * FROM templak@
------------------------------------------------------------------------------

COLUMN1             
--------------------
A                   

  1 record(s) selected.

laknar · #7 (**permalink**) 04-07-09, 23:17

having a scenario that

sometimes in TABLE3 may not have records.Hence the query will not retreive result.
in that scenario we should not update the TABLE1.(Because we are checking for not existence).

We have to update only when record present in TABLE3.

tonkuma · #8 (**permalink**) 04-08-09, 02:00

I thought that you can update table1 by an update statement.
Separate select statement for table1 would be not necessary.

I considered the scenario that coressponding rows not exists in table2, too.

Code:

UPDATE table1 target
   SET column2 = 'S'
 WHERE column2 = 'W'
   AND ('S', 'S')
       = ALL(
             SELECT
                    t1b.column2
                  , t3.field1
               FROM LATERAL
                  ( VALUES target.column1 ) AS t1a(column1)
               LEFT JOIN
                    table2 t2
                ON  t2.fun1 = t1a.column1
               LEFT JOIN
                    table3 t3
                ON  t3.field3 = t2.fun3
               LEFT JOIN
                    table1 t1b
                ON  t1b.column1 = t2.fun2
            )
;

laknar · #9 (**permalink**) 04-08-09, 03:12

you have used the table called LATERAL and alias called target.

is this the additional table to create.

TABLE1 and TABLE2 Always contains data.

When TABLE2 not matches with TABLE3 then should not update TABLE1.

When TABLE2 matches with TABLE3 then update TABLE1 by checking the existence like column2<>'S'.

tonkuma · #10 (**permalink**) 04-08-09, 04:26

LATERAL is a keyword to make accessible columns of outer table in nested table expressions(VALUES clause in my example).
Without LATERAL, "( VALUES target.column1 ) AS t1a(column1)" will get error.

There are some examples using LATERAL in the manual "SQL Reference Volume 1".
---> Chapter 5. Queries ---> Subselect ---> table-reference ---> Correlated references in table-references.

There is no new table to be created.

laknar · #11 (**permalink**) 04-08-09, 05:33

"DB2 v8.1.1.32"

the below query works

Code:

UPDATE table1 
   SET column2 = 'S'
 WHERE column2 = 'W'
   AND ('S', 'S')
       = ALL(
             SELECT t1b.column2,t3.file1 
                    FROM
                table1 t1a
                                 JOIN
                    table2 t2
                ON  t2.fun1 = t1a.column1
               LEFT JOIN
                     table3 t3
                ON  t3.file3 = t2.fun3
               LEFT JOIN
                     table1 t1b
                ON  t1b.column1 = t2.fun2
            ) AND COLUMN1='A';

but when we do filter in the empty table im getting empty result.
how to overcome that.
i have to get some value for those two columns.

"DB2 v8.1.1.32"

Code:

UPDATE table1 
   SET column2 = 'S'
 WHERE column2 = 'W'
   AND ('S', 'S')
       = ALL(
             SELECT t1b.column2,t3.file1 
                    FROM
                table1 t1a
                                 JOIN
                    table2 t2
                ON  t2.fun1 = t1a.column1
               LEFT JOIN
                     table3 t3
                ON  t3.file3 = t2.fun3
               LEFT JOIN
                     table1 t1b
                ON  t1b.column1 = t2.fun2 WHERE FILE2='20050102'
            ) AND COLUMN1='A';

tonkuma · #12 (**permalink**) 04-08-09, 06:39

Specify red phrase

Code:

              ON  t3.file3 = t2.fun3
              AND t3.file2 = '20050102'

instead of "WHERE FILE2='20050102'".

tonkuma · #13 (**permalink**) 04-08-09, 06:56

You are using DB2 V8.
So, please try my sample with using TABLE keyword instead of LATERAL keyword.

There are examples using TABLE keyword in the manual "IBM® DB2 Universal Database™ SQL Reference Volume 1 Version 8".
---> Chapter 4. Queries ---> Subselect ---> table-reference ---> Correlated references in table-references.
Compare Example 3: and Example 6:.

tonkuma · #14 (**permalink**) 04-08-09, 07:18

Delete double post.

laknar · #15 (**permalink**) 04-14-09, 21:44

both the queries have worked well.thank you.

1.another scenario is when FUN3(TABLE2) is NULL i dont really need to join TABLE3(FILE3).
straight away i can check for TABLE1(COLUMN2) and then update.

2.only if FUN3(TABLE2) is NOT NULL then i have to join with TABLE3(FILE3) and by checking both the columns from FILE1(TABLE3) AND TABLE1(COLUMN2) and then update.

Internet Services

Web Development

Digital Marketing

Consumer Tech