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
 

 

Query by the length of the field

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

New User


Joined: 14 Sep 2005
Posts: 74
Location: Kentucky, USA

PostPosted: Fri Mar 31, 2006 12:38 am    Post subject: Query by the length of the field
Reply with quote

Hi

Suppose I have a field whose definition is x(16). Now I only want to select those rows where the length of the data is > 9.

For example if I have data

12345
123456789
451728907354
152709038409
123456
142638394038101

Then using above data my query should return only following three rows

451728907354
152709038409
142638394038101


Please help me out with this.

Thanks,
Back to top
View user's profile Send private message

DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Fri Mar 31, 2006 3:48 am    Post subject: Re: Query by the length of the field
Reply with quote

muffirulz,

Using your example

Code:


DECLARE GLOBAL TEMPORARY TABLE TEMP_TBL           
  (                                               
       C_DATA_FIELD               CHAR(15)       
   )                                             
;                 

INSERT?.

SELECT C_DATA_FIELD     
  FROM SESSION.TEMP_TBL 
;                       

   +-----------------+
   |  C_DATA_FIELD   |
   +-----------------+
 1_| 12345           |
 2_| 123456789       |
 3_| 451728907354    |
 4_| 152709038409    |
 5_| 123456          |
 6_| 142638394038101 |
   +-----------------+ 

SELECT C_DATA_FIELD                     
  FROM SESSION.TEMP_TBL                 
 WHERE SUBSTR(C_DATA_FIELD,10,5) <> ' ' 
;                                       

   +-----------------+
   |  C_DATA_FIELD   |
   +-----------------+
 1_| 451728907354    |
 2_| 152709038409    |
 3_| 142638394038101 |
   +-----------------+



or

Code:


DECLARE GLOBAL TEMPORARY TABLE TEMP_TBL       
  (                                           
       V_DATA_FIELD            VARCHAR(15)     
   )                                           
;                                             

INSERT?.

SELECT V_DATA_FIELD         
  FROM SESSION.TEMP_TBL     
;                           

   +-----------------+
   |  V_DATA_FIELD   |
   +-----------------+
 1_| 12345           |
 2_| 123456789       |
 3_| 451728907354    |
 4_| 152709038409    |
 5_| 123456          |
 6_| 142638394038101 |
   +-----------------+
                     

SELECT V_DATA_FIELD                 
  FROM SESSION.TEMP_TBL             
 WHERE LENGTH(V_DATA_FIELD) > 9     

   +-----------------+
   |  V_DATA_FIELD   |
   +-----------------+
 1_| 451728907354    |
 2_| 152709038409    |
 3_| 142638394038101 |
   +-----------------+
                       



This what you?re looking for ?

Dave
Back to top
View user's profile Send private message
T-REXDB2

New User


Joined: 28 Apr 2005
Posts: 11
Location: USA

PostPosted: Fri Mar 31, 2006 10:07 am    Post subject: Re: Query by the length of the field
Reply with quote

muffirulz wrote:
Hi

Suppose I have a field whose definition is x(16). Now I only want to select those rows where the length of the data is > 9.

For example if I have data

12345
123456789
451728907354
152709038409
123456
142638394038101

Then using above data my query should return only following three rows

451728907354
152709038409
142638394038101


Please help me out with this.

Thanks,


Extremely simple answer to extremely simple question:

SELECT *

FROM SYSIBM.SYSTABLES

WHERE LENGTH(NAME) > 9


Hope this helps, muchly!!

;-]
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 Count the length of the record & ... sreekusr DFSORT/ICETOOL 4 Thu Mar 23, 2017 7:52 pm
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


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