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: 78
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: 10277
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: 10277
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: 78
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 Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts READ A PACKED "NEGATIVE" FI... jdesouza CA Products 3 Tue May 02, 2017 11:43 pm
No new posts Alter &DATENS field in HEADER1 Angad DFSORT/ICETOOL 4 Mon Apr 24, 2017 11:49 am
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


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