Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Addition in a query

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

Active User


Joined: 22 Dec 2005
Posts: 116

PostPosted: Tue Jan 15, 2008 2:03 pm    Post subject: Addition in a query
Reply with quote

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
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10350
Location: italy

PostPosted: Tue Jan 15, 2008 2:21 pm    Post subject: Reply to: Addition in a query
Reply with quote

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
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 967
Location: Richmond, Virginia

PostPosted: Tue Jan 15, 2008 9:50 pm    Post subject:
Reply with quote

Use substr to get the numeric part, then convert to numeric, add, then reconcatenate the "C" back in front.
Back to top
View user's profile Send private message
abhishekmdwivedi

New User


Joined: 22 Aug 2006
Posts: 95
Location: india

PostPosted: Wed Jan 16, 2008 5:40 pm    Post subject: Reply to: Addition in a query
Reply with quote

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
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 Query on IEFBR14 with GDG Ashishpanpaliya JCL & VSAM 3 Tue Nov 07, 2017 8:34 pm
No new posts Query on XMITIP abdulrafi All Other Mainframe Topics 1 Wed Oct 25, 2017 6:54 pm
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Addition of two records on 2 fields amar143 SYNCSORT 8 Tue Oct 03, 2017 11:14 am
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us