Query on SQL

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
bolastuff1975
Member
Posts: 8
Joined: Fri Sep 21, 2007 11:03 pm

Query on SQL

Post by bolastuff1975 » Sat Sep 22, 2007 12:04 am

bolastuff1975@yahoo.co.uk

Hi Please, help me out in solving this issue.

i need to update field2 & field3 of table-1 when there are more than 2 rows with same key-2 and field1.
field2 & field3 are picked from table2 based on key-1 and max time stamp for t1.

Table structre and data is shown below. i have also shown the sql where i am not able to get a unique row.

please, note bothe the tables contain differnet fields for update, i have not listed them b'cos they do not form a part of key.


Table1
-------
Key1 char
Key2 char
field1
field2
field3

data in table1
----------------

key1 key2 fiedl1 field2 field3
----- ----- ------ ------- ------
101 54 1001 blabla blabla
102 54 1001 blabla blabla
103 54 1001 blabla blabla
104 54 1001 blabla blabla
105 55 1001 blabla blabla
106 50 1002 blabla blabla

table2
-------
key1
field1
field2
field3
timestamp

Date in table2
----------------

key1 field1 field2 field3 timestamp
101 1001 abc abc t1
101 1001 abc abc t2
102 1001 abc abc t3
102 1001 abc abc t31
103 1001 abc abc t4
104 1001 abc abc t5

sql

update table1
set (b.field3,b.field4)
=(select field1,field2)
from table1 a,table-2 b
where a.key1 = b.key1
and timestamp = (select max(ts) from table2 c
where c.key1 = b.key1)
)
where a.key1=?? i need to retreive a unique key.

i tried the below inner query but resulted in more than 1 row

where a.key1 = (select c.key1 from table1 c
where c.field1 = a.field1
and c.key2 in ( select key2 from table1 d
where d.key1 = c.key1
and field1 = c.field1
group by key2
having count(*) > 1))

User avatar
DavidatK
Active Member
Posts: 65
Joined: Tue Mar 27, 2007 8:41 am
Location: Troy, MI USA

Post by DavidatK » Sat Sep 22, 2007 5:47 am

bolastuff1975,

Try this SQL

Code: Select all

DECLARE GLOBAL TEMPORARY TABLE TABLE1          
       (KEY1            CHAR(3)                
       ,KEY2            CHAR(2)                
       ,FIELD1          CHAR(4)                
       ,FIELD2          CHAR(6)                
       ,FIELD3          CHAR(6)                
        )                                      
;                                              
INSERT INTO SESSION.TABLE1 (KEY1, KEY2, FIELD1, FIELD2, FIELD3) 
       VALUES('101', '54', '1001', 'BLABLA', 'BLABLA');         
INSERT INTO SESSION.TABLE1 (KEY1, KEY2, FIELD1, FIELD2, FIELD3) 
       VALUES('102', '54', '1001', 'BLABLA', 'BLABLA');         
INSERT INTO SESSION.TABLE1 (KEY1, KEY2, FIELD1, FIELD2, FIELD3) 
       VALUES('103', '54', '1001', 'BLABLA', 'BLABLA');         
INSERT INTO SESSION.TABLE1 (KEY1, KEY2, FIELD1, FIELD2, FIELD3) 
       VALUES('104', '54', '1001', 'BLABLA', 'BLABLA');         
INSERT INTO SESSION.TABLE1 (KEY1, KEY2, FIELD1, FIELD2, FIELD3) 
       VALUES('105', '55', '1001', 'BLABLA', 'BLABLA');         
INSERT INTO SESSION.TABLE1 (KEY1, KEY2, FIELD1, FIELD2, FIELD3) 
       VALUES('104', '50', '1002', 'BLABLA', 'BLABLA');         
DECLARE GLOBAL TEMPORARY TABLE TABLE2       
       (KEY1            CHAR(3)             
       ,FIELD1          CHAR(4)             
       ,FIELD2          CHAR(6)             
       ,FIELD3          CHAR(6)             
       ,TS              CHAR(5)             
        )                                   
;                                           
INSERT INTO SESSION.TABLE2 (KEY1, FIELD1, FIELD2, FIELD3, TS)  
       VALUES('101', '1001', 'A11', 'A22', 'T1   ');           
INSERT INTO SESSION.TABLE2 (KEY1, FIELD1, FIELD2, FIELD3, TS)  
       VALUES('101', '1001', 'B11', 'B22', 'T2   ');           
INSERT INTO SESSION.TABLE2 (KEY1, FIELD1, FIELD2, FIELD3, TS)  
       VALUES('102', '1001', 'C11', 'C22', 'T3   ');           
INSERT INTO SESSION.TABLE2 (KEY1, FIELD1, FIELD2, FIELD3, TS)  
       VALUES('102', '1001', 'D11', 'D22', 'T31  ');           
INSERT INTO SESSION.TABLE2 (KEY1, FIELD1, FIELD2, FIELD3, TS)  
       VALUES('103', '1001', 'E11', 'E22', 'T4   ');           
INSERT INTO SESSION.TABLE2 (KEY1, FIELD1, FIELD2, FIELD3, TS)  
       VALUES('104', '1001', 'F11', 'F22', 'T5   ');           
SELECT *               
  FROM SESSION.TABLE1  
;                      
SELECT *               
  FROM SESSION.TABLE2  
;                      
UPDATE SESSION.TABLE1             T1                
   SET (FIELD2,FIELD3) =                            
       (SELECT FIELD2,FIELD3                        
          FROM SESSION.TABLE2     T2                
         WHERE T2.KEY1          = T1.KEY1           
           AND T2.TS =                              
               (SELECT MAX(TS)                      
                  FROM SESSION.TABLE2      T2A      
                 WHERE T2A.KEY1 = T1.KEY1           
                )                                   
        )                                           
 WHERE EXISTS                                       
       (SELECT COUNT(T1A.KEY2)                      
          FROM SESSION.TABLE1              T1A      
         WHERE T1A.KEY2         = T1.KEY2           
           AND T1A.FIELD1       = T1.FIELD1         
        HAVING COUNT(T1A.KEY2) > 2                  
        )                                           
;                                                   
SELECT *                
  FROM SESSION.TABLE1   
;                       
SELECT *                
  FROM SESSION.TABLE2   
;
Contents of the first two selects of tables, before the updates

Table1

Code: Select all

   +----------------------------------------+ 
   | KEY1 | KEY2 | FIELD1 | FIELD2 | FIELD3 | 
   +----------------------------------------+ 
 1_| 101  | 54   | 1001   | BLABLA | BLABLA | 
 2_| 102  | 54   | 1001   | BLABLA | BLABLA | 
 3_| 103  | 54   | 1001   | BLABLA | BLABLA | 
 4_| 104  | 54   | 1001   | BLABLA | BLABLA | 
 5_| 105  | 55   | 1001   | BLABLA | BLABLA | 
 6_| 104  | 50   | 1002   | BLABLA | BLABLA | 
   +----------------------------------------+ 
Table2

Code: Select all

    +-----------------------------------------+
    | KEY1 | FIELD1 | FIELD2 | FIELD3 |  TS   |
    +-----------------------------------------+
  1_| 101  | 1001   | A11    | A22    | T1    |
  2_| 101  | 1001   | B11    | B22    | T2    |
  3_| 102  | 1001   | C11    | C22    | T3    |
  4_| 102  | 1001   | D11    | D22    | T31   |
  5_| 103  | 1001   | E11    | E22    | T4    |
  6_| 104  | 1001   | F11    | F22    | T5    |
    +-----------------------------------------+
Contents of the second two selects of tables, after the update

Table1

Code: Select all

   +----------------------------------------+
   | KEY1 | KEY2 | FIELD1 | FIELD2 | FIELD3 |
   +----------------------------------------+
 1_| 101  | 54   | 1001   | B11    | B22    |
 2_| 102  | 54   | 1001   | D11    | D22    |
 3_| 103  | 54   | 1001   | E11    | E22    |
 4_| 104  | 54   | 1001   | F11    | F22    |
 5_| 105  | 55   | 1001   | BLABLA | BLABLA |
 6_| 104  | 50   | 1002   | BLABLA | BLABLA |
   +----------------------------------------+
Table2

Code: Select all

   +-----------------------------------------+
   | KEY1 | FIELD1 | FIELD2 | FIELD3 |  TS   |
   +-----------------------------------------+
 1_| 101  | 1001   | A11    | A22    | T1    |
 2_| 101  | 1001   | B11    | B22    | T2    |
 3_| 102  | 1001   | C11    | C22    | T3    |
 4_| 102  | 1001   | D11    | D22    | T31   |
 5_| 103  | 1001   | E11    | E22    | T4    |
 6_| 104  | 1001   | F11    | F22    | T5    |
   +-----------------------------------------+
                                              
Is this the result you were looking for? In the requirements at in your post you said ?more than 2 rows? which means 3 or more. If you wanted 2 or more change the ?> 2? to ?> 1?.


If you have any questions about the Update SQL, please do not hesitate to come back and ask.


Dave

bolastuff1975
Member
Posts: 8
Joined: Fri Sep 21, 2007 11:03 pm

Post by bolastuff1975 » Mon Sep 24, 2007 7:27 pm

Dave,
when i try it here, i get a message taht the inner query is listing more than 1 row.

I beleive 'where exists' condition checks if KEY2 is satisfying our update criteria and results more than 1 key1 but I need to pick single row(KEY1) for updates.

Thanks

User avatar
DavidatK
Active Member
Posts: 65
Joined: Tue Mar 27, 2007 8:41 am
Location: Troy, MI USA

Post by DavidatK » Tue Sep 25, 2007 4:12 am

bolastuff1975,

So, your getting a -811.

The ?WHERE EXISTS? returns only a ?True|False? indicator and will not give you a -811,

Nor will the innermost SET ?SELECT MAX(TS)?. There can only be one MAX timestamp, even if multiple rows have this timestamp. Therefore it must be giving you the -811 in the SET outer ?SELECT? This will occur if you have multiple rows on Table2 with the same KEY1 and Timestamp.

What version of DB2 are you using ?

Veera
Moderator
Posts: 111
Joined: Wed Feb 22, 2006 2:59 pm

Post by Veera » Tue Sep 25, 2007 4:51 am

bolastuff1975,

I agree with Dave..explanation for resulting in more than one row in the
co-realted sub-query.

Even the below query what you have given in your initial post.

update table1
set (b.field3,b.field4)
=(select field1,field2)
from table1 a,table-2 b
where a.key1 = b.key1
and timestamp = (select max(ts) from table2 c
where c.key1 = b.key1)
)

Assume the below data for 2 fields in question

KEY1 TS
100 100
100 100
100 99

In this scenario for max(TS) for a key1=100 the query will give the
first 2 rows , the only scenario under which you will get a unique row is
only when you have a distinct value for a TS field.

Now what is important is

1) Whether more than one rec can have the same TS in PRODUCTION
or live scenario...may be your test data has 2 rows with same TS.

You need to cross check it, I blive that if you have different timestamps
then it will result in only one row.

2) If yes you can have same TS for more than one row

Then its just a thought what you can possibly do ,if your
requirement allows is add equality of one more field
say FIELD-NEW which has a distinct values for a max(TS)


where a.key1 = b.key1
and timestamp = (select max(ts) from table2 c
where c.key1 = b.key1
and C.FIELD-NEW = B.FIELD-NEW)
)



3) If TS will not be same for more than one rec then your intial query
should be good.

Please let us know how you have finally handled it.

Thanks,
Veera.

bolastuff1975
Member
Posts: 8
Joined: Fri Sep 21, 2007 11:03 pm

Post by bolastuff1975 » Wed Sep 26, 2007 6:21 pm

Dave & Veera,

Thanks, very much for u r help. It was not a problem with u r query. it was due to the data in the table.

Cheers

bolastuff1975
Member
Posts: 8
Joined: Fri Sep 21, 2007 11:03 pm

Post by bolastuff1975 » Thu Sep 27, 2007 11:20 pm

Further, to the given query. i am intrested in findign the records that have timestamp < ts3.
Assuming t5>t4>t31>t3>t2>t1.
i odnt want to update 103, 104 of KEY1.

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others