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
 
Fetch the rows where length in one column should be 36

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

New User


Joined: 26 Sep 2007
Posts: 42
Location: pune

PostPosted: Tue Jan 29, 2008 12:32 pm    Post subject: Fetch the rows where length in one column should be 36
Reply with quote

HI

I want to fetch the rows from the table where length of the data in one column should be 36 do have any operator to find the lenth of the data in the column
quer is some thing like this

select column1,column2

from table

where (i want to check lenth of data in column2 =36)
i want to RUN it in SPUFI plz help me
Back to top
View user's profile Send private message

rag swain

New User


Joined: 17 Dec 2007
Posts: 33
Location: pune,INDIA

PostPosted: Tue Jan 29, 2008 5:16 pm    Post subject: Reply to: Fetch the rows where length in one column should b
Reply with quote

You can use the LENGTH function in the where clause in your query, but then need to consider if the culumn is delared as NULL, VARCHAR or FIXED etc. If you post those decalration,someone here would have a pointer towards a more aprropriate solution.

[/code]
Back to top
View user's profile Send private message
mosinjamadar

New User


Joined: 26 Sep 2007
Posts: 42
Location: pune

PostPosted: Wed Jan 30, 2008 10:47 am    Post subject: Reply to: Fetch the rows where length in one column should b
Reply with quote

Hi

The column data type is CHAR and it is NOT NULL
Back to top
View user's profile Send private message
abhishekmdwivedi

New User


Joined: 22 Aug 2006
Posts: 95
Location: india

PostPosted: Wed Jan 30, 2008 11:26 am    Post subject: Reply to: Fetch the rows where length in one column should b
Reply with quote

Here's an example :

Code:
Select col1,col2 from table where length(col2)=36;
Back to top
View user's profile Send private message
mosinjamadar

New User


Joined: 26 Sep 2007
Posts: 42
Location: pune

PostPosted: Wed Jan 30, 2008 2:06 pm    Post subject: Reply to: Fetch the rows where length in one column should b
Reply with quote

Hi

But inSPUFI i am getting all the rows in which col2 data length is not 36
my query is
SELECT SB_SRVC_DES
FROM GIBIT02.GIB_PRFR_LIST
WHERE LENGTH(SB_SRVC_DES) = 36;

plase clerify it
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Wed Jan 30, 2008 3:01 pm    Post subject:
Reply with quote

try STRIP... as
Code:

 WHERE LENGTH(STRIP(SB_SRVC_DES)) = 36;


search for STRIP to see its parameters.
Back to top
View user's profile Send private message
rag swain

New User


Joined: 17 Dec 2007
Posts: 33
Location: pune,INDIA

PostPosted: Wed Jan 30, 2008 6:16 pm    Post subject: Reply to: Fetch the rows where length in one column should b
Reply with quote

,or, you can use
Code:


WHERE  (LENGTH(RTRIM(COL_NAME))) = 36; 
 
Back to top
View user's profile Send private message
mosinjamadar

New User


Joined: 26 Sep 2007
Posts: 42
Location: pune

PostPosted: Wed Jan 30, 2008 7:28 pm    Post subject:
Reply with quote

thanks a lot it is working
Back to top
View user's profile Send private message
mosinjamadar

New User


Joined: 26 Sep 2007
Posts: 42
Location: pune

PostPosted: Wed Jan 30, 2008 7:30 pm    Post subject:
Reply with quote

You can use the LENGTH function in the where clause in your query, but then need to consider if the culumn is delared as NULL, VARCHAR or FIXED etc.
can u plz tell me whay u asked above thing
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Wed Jan 30, 2008 7:35 pm    Post subject:
Reply with quote

If the field is a CHAR it has a fixed length of 36, which is why you were getting all rows. If it is VARCHAR it can vary in length. If it allows NULL you may have issues when executing the LENGTH(column) when the row has a value of NULL.
Back to top
View user's profile Send private message
rag swain

New User


Joined: 17 Dec 2007
Posts: 33
Location: pune,INDIA

PostPosted: Wed Jan 30, 2008 7:41 pm    Post subject: Reply to: Fetch the rows where length in one column should b
Reply with quote

Thanks! Stodolas, you said it all what i intended to, actally i was compiling a reply but came accross yours at the same time.
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 records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts Fetch Previous month & year in MM... girishb2 DFSORT/ICETOOL 3 Thu Sep 21, 2017 9:54 pm
No new posts Conditional highlighting of rows in I... packerm CLIST & REXX 2 Thu Sep 21, 2017 6:50 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
This topic is locked: you cannot edit posts or make replies. Selecting two copybooks of different ... Vignesh Sid COBOL Programming 8 Tue Sep 05, 2017 7:28 pm

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