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
 

 

For update of

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

New User


Joined: 02 Aug 2007
Posts: 83
Location: Here

PostPosted: Fri Oct 05, 2012 4:51 pm    Post subject: For update of
Reply with quote

I have cursor declared as following using the UNION ALL clause. I need to write into a report the details in this cursor. this is why its coded with different union all.

SELECT * FROM Table A WHERE Action = 'INP'
AND Time_STAMP = Current timestamp
AND STATUS= A
UNION ALL
SELECT * FROM Table B WHERE Action = 'BYP'
AND Time_Stamp = Current timestamp
AND STATUS= A
UNION ALL
SELECT * FROM Table B WHERE Action = 'CRT'
AND Time_Stamp = Old timestamp
AND STATUS= A
UNION ALL
SELECT * FROM Table B WHERE Action = 'DEL'
AND Time_Stamp = Old timestamp
AND STATUS= A

Table A looks as follows

Code:


TRANM_ID                ACTION            DATA           TIME_STAMP                STATUS
ABC                     INP               ZZXX           Old Timestamp               A
ABC                     CRT               ZZXX           Old Timestamp               A
ABC                     DEL               ZZXX           Old Timestamp               A


             


As I write each of the rows in the cursor to the report i need to update the STATUS column to C. Can this be acheived in a cursor that uses UNION ALL. please advise?
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Fri Oct 05, 2012 6:24 pm    Post subject:
Reply with quote

No,
you have two tables,
(which is why you went to UNION
otherwise, you would have had to qualify the columns)
which would cause the cursor to be declared as READ-ONLY.

That by the way is in the manual.
Back to top
View user's profile Send private message
CJT

New User


Joined: 02 Aug 2007
Posts: 83
Location: Here

PostPosted: Fri Oct 05, 2012 6:42 pm    Post subject:
Reply with quote

Ok, So would this mean i will need to update the status column to C in a separate query for the given time stamp?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Fri Oct 05, 2012 7:26 pm    Post subject:
Reply with quote

actually,
you would need to use one of two separate UPDATE's,
(one for table A, one for Table B)
remembering of course,
you need to know from which table the row has been selected.

do you have an order by clause in your cursor?
Back to top
View user's profile Send private message
CJT

New User


Joined: 02 Aug 2007
Posts: 83
Location: Here

PostPosted: Fri Oct 05, 2012 7:50 pm    Post subject:
Reply with quote

Hi Dick,

Please pardon my typo here. the query is only about one table - Table A. Table B was a typo. I am also using Order By

SELECT * FROM Table A WHERE Action = 'INP'
AND Time_STAMP = Current timestamp
AND STATUS= A
UNION ALL
SELECT * FROM Table A WHERE Action = 'BYP'
AND Time_Stamp = Current timestamp
AND STATUS= A
UNION ALL
SELECT * FROM Table A WHERE Action = 'CRT'
AND Time_Stamp = Old timestamp
AND STATUS= A
UNION ALL
SELECT * FROM Table A WHERE Action = 'DEL'
AND Time_Stamp = Old timestamp
AND STATUS= A
Order by Data, Action.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Oct 05, 2012 8:00 pm    Post subject:
Reply with quote

is something wrong with this query (besides the syntax errors I took over):
Code:
SELECT * FROM Table A
WHERE Action in ( 'INP','BYP','CRT','DEL' )
AND Time_Stamp = case when Action in ( 'INP','BYP') then Old timestamp else current timestamp end
AND STATUS= A
Order by Data, Action
Back to top
View user's profile Send private message
CJT

New User


Joined: 02 Aug 2007
Posts: 83
Location: Here

PostPosted: Mon Oct 08, 2012 11:55 am    Post subject:
Reply with quote

Thanks, but i have one question. Can i make the Stat_C = A for the rows processed from A to C using update clause. I am not well versed with DB2, so if you can advise it would be good help.
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 Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
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 -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm


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