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: 75
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: 10232
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: 10232
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: 75
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 SORT VSAM file with each field one by... maxsubrat DFSORT/ICETOOL 6 Tue Mar 14, 2017 1:07 pm
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 IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm
No new posts outrec field outside range Danielle.Filteau SYNCSORT 10 Sat Mar 04, 2017 2:37 am
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm


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