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: 1281
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 XML Parse for COBOL 5.2 Bhanu Praveen COBOL Programming 1 Sat Jul 08, 2017 8:58 pm
No new posts Dynamic output file creation in cobol... smileheal COBOL Programming 7 Thu Jun 15, 2017 10:53 pm
No new posts Search utility(3.14) using JCl and Cobol RKS3 COBOL Programming 12 Sat Jun 10, 2017 10:24 pm
No new posts COBOL DB2 program - zIIP eligible vasanthz COBOL Programming 7 Wed May 31, 2017 5:12 am
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm


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