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
 

 

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: 10234
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: 962
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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


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