| View previous topic :: View next topic |
| Author |
Message |
YVRAMJ
Beginner
Joined: 18 Jan 2003
Posts: 12
Topics: 7
|
 Posted: Fri May 16, 2003 10:41 pm Post subject: Can we use a CASE statement in UPDATE SQL |
 |
|
Hi,
I need sql for the following condition.
I have a column PO_NUM CHAR(06) in a DB2 table PURHDR. It's value should be in the range 60000 - 699999. Now I need one SQL to update its value incrementing by 1 every time I make an update on this table.
I maintain this record separately by keeping a unique value for this record in key column. (" nextpo ")
For eg.. if it's value is 600001 the update statement
should set it to 600002 .. But if it's value reaches 699999 then it should
roll over back to 600000.
The query goes like this to increment by 1..
UPDATE TXDBT01.PURHDR
SET PO_NUM = ( SELECT CHAR(INTEGER(PO_NUM) + 1)
FROM TXDBT01.PURHDR
WHERE KEY_COLUMN = 'NEXTPO')
But this won't check and see if it reached the limit 69999.
I am not sure how to use case statement or any other way to check limit and set it back to 60000. If any one got ideas please let me know.
Thanks,
Ram. |
|
| Back to top |
|
 |
Manas Biswal
Intermediate
Joined: 29 Nov 2002
Posts: 382
Topics: 27
Location: Chennai, India
|
| Posted: Wed Jun 25, 2003 2:09 pm Post subject: |
|
|
Well...U can do this in two queries I guess. You can modify the query you have just written to include another WHERE condition where you check for the maximum value of 69999.
In another query executed just after this query, you can set all 69999 to 60000.
Regards,
Manas |
|
| Back to top |
|
|
coolman
Intermediate
Joined: 03 Jan 2003
Posts: 283
Topics: 27
Location: US
|
| Posted: Mon Jun 30, 2003 1:46 am Post subject: |
|
|
Ram,
I have a question here. Why not just code like this :
| Code: |
update table
set po_num = po_num + 1
where po_num < 69999
|
Why do you have to maintain it in a separate key column. BTW, is the table indexed on po_num? I
If you are doing it through a Program, you would get an SQLCODE of 100, when 69999 is reached, then you can reset it back to 0 and proceed.
Hope this helps...
Cheers,
Coolman.
________
magic flight launch box review
Last edited by coolman on Sat Feb 05, 2011 1:25 am; edited 1 time in total |
|
| Back to top |
|
|
Himesh
CICS Forum Moderator
Joined: 20 Dec 2002
Posts: 80
Topics: 21
Location: Chicago
|
| Posted: Wed Jul 02, 2003 11:45 am Post subject: |
|
|
Hi Ram,
Please check if the below SQL will suffice.
I am under the impression that, you are maintaining a particular row in the table txdbt01.purhdr (for a value of "NEXTPO"), and that you keep incrementing the value in the column called po_num for that row.
| Code: |
UPDATE
txdbt01.purhdr
SET po_num =
(SELECT
CASE WHEN po_num = 699999
THEN 600000
ELSE (po_num+1)
END
FROM txdbt01.purhdr
WHERE
key_column = 'NEXTPO')
|
ps: You might need to add a (WHERE key_column = 'NEXTPO') clause for the outer query to update only the specific record.
regards,
Himesh |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
