IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

To fetch more than 9 characters for a field through query


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
maxsubrat

Active User


Joined: 27 Feb 2008
Posts: 110
Location: india

PostPosted: Fri Jan 13, 2012 4:34 pm
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
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Fri Jan 13, 2012 7:06 pm
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
Reply with quote

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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Fri Jan 13, 2012 7:15 pm
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
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

Active User


Joined: 27 Feb 2008
Posts: 110
Location: india

PostPosted: Fri Jan 13, 2012 10:12 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Substring number between 2 characters... DFSORT/ICETOOL 2
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Reading dataset in Python - New Line ... All Other Mainframe Topics 22
Search our Forums:

Back to Top