|
View previous topic :: View next topic
|
| Author |
Message |
pjnithin
Active User
Joined: 22 Dec 2005
Posts: 116
|
|
|
|
I want to add 1 to a colum selected in a query.
The query is like :
SELECT MAX(EMPNO) + 1
FROM UPPSU.TKGFIDSG
WHERE EMPNO LIKE 'C%'
But here the field EMPNO is a character field. So adding 1 to this in the wuery is failing. Also I am selecting only Those EMPNO whose first character is a 'C'. So can anyone get me a solution houw to 1 one to the maximum value of EMPNO selected.
Like if the max value of EMPNO is C111223333, the value after addition should be C111223334.
Please let me know whether this is possible by making some changes to the query. |
|
| Back to top |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
as usual the forum is asked to put a remedy to poor application design!
if the field is char then it is just that .. a char
the request made by the application analyst is to use a kind word illogic,
if You search the net You might find some useful ideas on how to
write an UDF to do a casting from char to integer,
("db2 query cast char integer")
but since You are just retrieving the column, I guess it would be simpler to carry on
the arithmetics outside the query |
|
| Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006
Posts: 1042
Location: Richmond, Virginia
|
|
|
|
| Use substr to get the numeric part, then convert to numeric, add, then reconcatenate the "C" back in front. |
|
| Back to top |
|
|
abhishekmdwivedi
New User
.jpg)
Joined: 22 Aug 2006
Posts: 95
Location: india
|
|
|
|
pjnithin,
The design is certainly a poor one but following code may do what you asked for :
| Code: |
SELECT INT(SUBSTR('C111222333',2,9))+1 FROM SYSIBM.SYSDUMMY1;
|
|
|
| Back to top |
|
|
|
|
