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
 
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 Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Remove leading spaces from numeric field rexx77 SYNCSORT 6 Wed Sep 06, 2017 2:15 am
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
No new posts JES2 job size field matching Windows ... SRICOBSAS All Other Mainframe Topics 4 Tue Sep 05, 2017 5:49 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am

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