Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2008
    Posts
    94

    Unanswered: HOW CAN i write this in single query?

    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

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

  3. #3
    Join Date
    Jul 2008
    Posts
    94
    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.

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

  5. #5
    Join Date
    Jul 2008
    Posts
    94
    thank you tonkuma,it was the mistake of mine.
    got the result as expected.
    Last edited by laknar; 04-07-09 at 10:09.

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

  7. #7
    Join Date
    Jul 2008
    Posts
    94
    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.

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

  9. #9
    Join Date
    Jul 2008
    Posts
    94
    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'.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 04-08-09 at 05:33.

  11. #11
    Join Date
    Jul 2008
    Posts
    94
    "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';

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Specify red phrase
    Code:
                  ON  t3.file3 = t2.fun3
                  AND t3.file2 = '20050102'
    instead of "WHERE FILE2='20050102'".

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

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Delete double post.

  15. #15
    Join Date
    Jul 2008
    Posts
    94
    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.

Posting Permissions

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