Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Update the Row which is selected - Cobol Db2 pgm ..

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

New User


Joined: 23 Jul 2010
Posts: 10
Location: hyderabad

PostPosted: Sun Jul 15, 2012 3:41 pm    Post subject: Update the Row which is selected - Cobol Db2 pgm ..
Reply with quote

Hello ,

I have a Client Address (CAD) Table with columns :
Client_no, Ad_type_cd,Str1,Str2,St_cd,Pin_cd ..... Suppose I have the AD_type values as 'HMM','OTH','RES' and so on ..

NOTE : Client number is not unique. For one client number I can get all the address type rows ....

I have to sort the rows with the first preference as hmm for a Client .Even If hmm is not found then which ever comes first should be taken and that particular row shud be updated (multiple columns but not client_no, Ad_type )

Select Client_no, Str1,Str2 , (Case Ad_type When 'HMM' 0 else 1 End) as Sort1 from CAD Where client _no = Ws:client_no Order by Sort1
Fetch First Row only ;

If Sqlcode = 0
Perform Update_para.

I am not sure how to write the update para for the row selected in the above statement.

Please Suggest me If I can do the same with in a Single Query ( may be with subquery or Where Exists .. What ever )
Thanks in Advance .....
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Jul 16, 2012 2:01 am    Post subject:
Reply with quote

Hello,

Post some sample rows that demonstrate the possible combinations of the data and how you want your process to work. The better samples you post, the more likely you are to get a usable reply.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Mon Jul 16, 2012 12:15 pm    Post subject:
Reply with quote

in the assumption that Client_no,ad_type is a unique key :
Code:
update CAD A
set ...
where A.Client_no = :Ws-client-no
and A.Ad_type in
(
Select B.ad_type from CAD B Where B.client _no = A.Client_no
Order by  Case When B.Ad_type = 'HMM' then 0 else 1 End
Fetch First Row only 
)
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 RULES(NOEVENPACK) in cobol jackzhang75 COBOL Programming 5 Wed Mar 29, 2017 12:47 am
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Execute TSO ACF commands from COBOL-I... supreethi.srid TSO/ISPF 0 Tue Feb 21, 2017 5:19 pm
No new posts Mainframe Interview Questions CICS,CO... akshathan Mainframe Interview Questions 6 Thu Feb 09, 2017 6:38 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us