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
 

 

To fetch more than 9 characters for a field through query

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

New User


Joined: 27 Feb 2008
Posts: 72
Location: india

PostPosted: Fri Jan 13, 2012 4:34 pm    Post subject: To fetch more than 9 characters for a field through query
Reply with quote

Hi,
I have a requirement to fetch emp-id which are 10 characters(digits and alphabets) only.

I tried with the below query,
SELECT SUBSTR(EMP_ID,1,10)
FROM PO.ACVFTPO ;

I am getting output of emp-id with 9 characters only, not 10 characters records...
Can someone give some idea... if we fetch more than 9 characters also, i think it is ok. but the problem is the emp-id is a alphanumeric.

Thanks
Back to top
View user's profile Send private message

gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Jan 13, 2012 6:57 pm    Post subject:
Reply with quote

Try something like

Code:
SELECT EMP_ID
FROM PO.ACVFTPO
where STRIP(LENGTH(EMP_ID)) = 10  ;


Assuming EMP_IS is CHAR column.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10210
Location: italy

PostPosted: Fri Jan 13, 2012 7:06 pm    Post subject: Reply to: To fetch more than 9 characters for a field throug
Reply with quote

Code:
STRIP(LENGTH(EMP_ID))

I am pretty sure that no dbms will ever support columns/fields with a length expressed by a 10 digit number icon_cool.gif

most probably it would be wiser to code
Code:
LENGTH(STRIP(EMP_ID))
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Jan 13, 2012 7:13 pm    Post subject:
Reply with quote

Thanks Enrico... Typo Error icon_smile.gif
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10210
Location: italy

PostPosted: Fri Jan 13, 2012 7:15 pm    Post subject: Reply to: To fetch more than 9 characters for a field throug
Reply with quote

I knew it was a typo... just picking a bit icon_biggrin.gif
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri Jan 13, 2012 7:28 pm    Post subject: Re: To fetch more than 9 characters for a field through quer
Reply with quote

maxsubrat wrote:
Hi,
I tried with the below query,
SELECT SUBSTR(EMP_ID,1,10)
FROM PO.ACVFTPO ;

I am getting output of emp-id with 9 characters only, not 10 characters records...
Thanks

Code:
SELECT EMP_ID FROM PA.ACVFTPO;

This will get you the full EMP_ID. What is the definition of EMP_ID and have you checked to see if there are any with more then 9 characters?
Back to top
View user's profile Send private message
maxsubrat

New User


Joined: 27 Feb 2008
Posts: 72
Location: india

PostPosted: Fri Jan 13, 2012 10:12 pm    Post subject: Reply to: To fetch more than 9 characters for a field throug
Reply with quote

Thanks enrico and gilbharat... query working fine ...
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 Join in SQL Query vickey_dw DB2 1 Tue Jan 17, 2017 12:15 am
No new posts INVALID FIELD OR CONSTANT IN SORTOF ?? Ron Klop DFSORT/ICETOOL 8 Wed Jan 11, 2017 3:44 pm
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am


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