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
 
Problem using DIGITS() and SUBSTR()

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

New User


Joined: 02 Jun 2008
Posts: 6
Location: Ottawa, Canada

PostPosted: Mon Jun 23, 2008 8:18 pm    Post subject: Problem using DIGITS() and SUBSTR()
Reply with quote

Folks, I got a rookie problem with SQL. I have an SQL query with the following statement: icon_confused.gif

SUBSTR(DIGITS(b.SIN),2,9) where the SIN is an INTEGER field that I need to extract the 9 digits - hence the 2,9.

Problem is that the output from this has leading characters ".&" (quotes added for clarity).

.&114172661

The leading characters are the problem. I have knowledge of functions like CHAR() from my days working with SQL SERVER and RDBMS, JCL on the HP and VAX.

I was thinking about replacing the SUBSTR(DIGITS( functions with a CHAR statement like this: CHAR(b.SIN,2,9)

Would this solve the problem? Your sage advice and wisdom would be gratefully appreciated. Jon icon_redface.gif
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Jun 23, 2008 8:26 pm    Post subject:
Reply with quote

DIGITS function can take SMALLINT, INTEGER or DECIMAL as argument..

y dont you try SUBSTR(CHAR(b.SIN),3,9) to get the 9 digits after omitting ".&" ??
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 LMINIT problem - not finding DDNAME Danielle.Filteau CLIST & REXX 7 Tue Sep 19, 2017 9:57 pm
No new posts Problem commiting to DB2 from IMS MPP Ole Soerensen IMS DB/DC 2 Wed Jul 26, 2017 5:19 pm
No new posts SUBSTR for Date functions Shaheen Shaik DB2 4 Thu Apr 06, 2017 2:14 pm
No new posts Strings with double quotes having pro... raja Arumugam All Other Mainframe Topics 11 Thu Mar 30, 2017 10:34 am
No new posts Problem reading GTF trace output danik56 ABENDS & Debugging 7 Thu Mar 16, 2017 1:02 pm

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