Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Need Guidance regarding UPDATE query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 19
Location: India

PostPosted: Mon Feb 12, 2018 11:35 pm    Post subject: Need Guidance regarding UPDATE query
Reply with quote

Hi All,

I am trying to write an UPDATE query for updating the rows which are fulfilling the below criteria :

a) Company Code & Master Id should be present in both the tables - DEPCT table & CONTE table
b) TOTAL_NUM_OF_LAPSE should be less than 0 in DEPCT table for those master_id & Company Code which are present in both tables
c) SEGMENT_ID should be spaces in DEPCT table for master_id & Company Code which are present in both tables
d) POLICY_STATUS should not be equal to 'P' in CONTE table for master_id & Company Code which are present in both tables

This is the UPDATE query which i have written :
Code:
UPDATE V1LFODCJ.DEPCT                           
SET TOTAL_NUM_OF_LAPSE = 0                     
WHERE MASTER_ID                                 
IN (SELECT B.MASTER_ID FROM V1LFODCJ.CONTE A,   
    V1LFODCJ.DEPCT B WHERE                     
       A.MASTER_ID = B.MASTER_ID AND           
       A.COMPANY_CODE = B.COMPANY_CODE AND     
       B.TOTAL_NUM_OF_LAPSE < 0 AND             
       B.SEGMENT_ID = ' ' AND                   
       A.POLICY_STATUS <> 'P');   



This is the data in DEPCT table before running the UPDATE query :
Code:
COMPANY_CODE  MASTER_ID        SEGMENT_ID  TOTAL_NUM_OF_LAPSE
CHAR(3)       CHAR(15)         CHAR(2)               DEC(5,0)
------------  ---------------  ----------  ------------------
**************************** TOP OF DATA ********************
CGA                0000013477                              0.
CGA                0000013477                             -1.
CGA                0000013477                             -1.
CGA                0000013477  JA                         -1.
001                  G1105177                             -1.
001                  G1105177                             -1.
001                  G1105177                             -1.
001                  G1105177  IA                         -1.
001                  G1105177  JA                         -1.



This is the data in CONTE table :

Code:
COMPANY_CODE  MASTER_ID        POLICY_STATUS
CHAR(3)       CHAR(15)         CHAR(1)     
PRIMARY-----  PRIMARY--------  -------------
**************************** TOP OF DATA ***
CGA                0000013477  A
001                  G1105177  A             



This is the data in DEPCT table after running the UPDATE query :
Code:
COMPANY_CODE  MASTER_ID        SEGMENT_ID  TOTAL_NUM_OF_LAPSE
CHAR(3)       CHAR(15)         CHAR(2)               DEC(5,0)
------------  ---------------  ----------  ------------------
**************************** TOP OF DATA *********************
CGA                0000013477                              0.
CGA                0000013477                              0.
CGA                0000013477                              0.
CGA                0000013477  JA                          0.
001                  G1105177                              0.
001                  G1105177                              0.
001                  G1105177                              0.
001                  G1105177  IA                          0.
001                  G1105177  JA                          0.

The TOTAL_NUM_OF_LAPSE field is becoming 0 for all the values even for the rows where SEGMENT_ID is not equal to spaces.

Expected result in DEPCT table :
Code:
COMPANY_CODE  MASTER_ID        SEGMENT_ID  TOTAL_NUM_OF_LAPSE
CHAR(3)       CHAR(15)         CHAR(2)               DEC(5,0)
------------  ---------------  ----------  ------------------
**************************** TOP OF DATA ********************
CGA                0000013477                              0.
CGA                0000013477                              0.
CGA                0000013477                              0.
CGA                0000013477  JA                         -1.
001                  G1105177                              0.
001                  G1105177                              0.
001                  G1105177                              0.
001                  G1105177  IA                         -1.
001                  G1105177  JA                         -1.


I have also tried the below UPDATE query but output is still same. It has changed the TOTAL_NUM_OF_LAPSE to 0 for all the rows including the ones where SEGMENT_ID is not equal to spaces.
Code:
UPDATE V1LFODCJ.DEPCT                                         
SET TOTAL_NUM_OF_LAPSE = 0                                     
WHERE EXISTS                                                   
(SELECT A.COMPANY_CODE,A.MASTER_ID FROM V1LFODCJ.CONTE A,     
            V1LFODCJ.DEPCT B                                   
 WHERE A.COMPANY_CODE = B.COMPANY_CODE AND                     
       A.MASTER_ID = B.MASTER_ID AND                           
       B.TOTAL_NUM_OF_LAPSE < 0 AND                           
       B.SEGMENT_ID = ' ' AND                                 
       A.POLICY_STATUS <> 'P');                               


Please let me know what i am missing here and how can i achieve the expected output.

Thanks a lot in Advance !
Back to top
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2307
Location: @my desk

PostPosted: Tue Feb 13, 2018 12:56 am    Post subject:
Reply with quote

You might want to move your DEPCT checks outside your sub query (shown in the original post) like this:
Code:
UPDATE V1LFODCJ.DEPCT                           
 SET TOTAL_NUM_OF_LAPSE = 0                     
 WHERE  TOTAL_NUM_OF_LAPSE < 0 AND             
        SEGMENT_ID = ' ' AND   
 MASTER_ID                                 
 IN (SELECT B.MASTER_ID FROM V1LFODCJ.CONTE A,   
     V1LFODCJ.DEPCT B WHERE                     
        A.MASTER_ID = B.MASTER_ID AND           
        A.COMPANY_CODE = B.COMPANY_CODE AND     
        A.POLICY_STATUS <> 'P');   
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 19
Location: India

PostPosted: Sat Feb 17, 2018 5:46 pm    Post subject:
Reply with quote

Thanks a lot Arun for taking a look and pointing me in right direction. Your query worked fine and i am able to get the expected output.

icon_smile.gif
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2307
Location: @my desk

PostPosted: Tue Feb 20, 2018 9:43 am    Post subject:
Reply with quote

Thanks for letting know!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Query to skip Max time-stamp and fetc... vickey_dw DB2 8 Sun Jan 28, 2018 1:09 am
No new posts reg query on DYNALLOC feature raghuraman123 SYNCSORT 12 Wed Jan 10, 2018 2:42 pm
No new posts Conversion of DLI update Jobs as BMP ... Gopinath Shanmugam IMS DB/DC 3 Wed Dec 20, 2017 12:02 pm
No new posts query to fetch record which has only ... maxsubrat DB2 12 Mon Dec 11, 2017 5:03 pm
No new posts How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 4 Wed Nov 22, 2017 10:23 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us