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
 
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: 82
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: 10326
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: 10326
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: 82
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 line 52: Name or string > 250 char... Ashishpanpaliya CLIST & REXX 5 Sat Oct 14, 2017 2:29 am
No new posts sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Partial color change of a field in CI... waseem0424 CICS 5 Fri Sep 29, 2017 7:56 pm
No new posts Fetch Previous month & year in MM... girishb2 DFSORT/ICETOOL 3 Thu Sep 21, 2017 9:54 pm

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