For update of

CJT · New User Joined: 02 Aug 2007 Posts: 83 Location: Here

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

dbzTHEdinosauer · Posted: Fri Oct 05, 2012 6:24 pm

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.

CJT · New User Joined: 02 Aug 2007 Posts: 83 Location: Here

Ok, So would this mean i will need to update the status column to C in a separate query for the given time stamp?

dbzTHEdinosauer · Posted: Fri Oct 05, 2012 7:26 pm

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?

CJT · New User Joined: 02 Aug 2007 Posts: 83 Location: Here

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.

GuyC · Posted: Fri Oct 05, 2012 8:00 pm

is something wrong with this query (besides the syntax errors I took over):

CJT · New User Joined: 02 Aug 2007 Posts: 83 Location: Here

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.