RE: Constructing Sql to position phone number that breakup in areacode, prefix, suffix and extension

- That is what I've been struggling with.  

- The obvious problem with the statement provided is that if user enter and 614 
areaCode, all the of 614 will be display regardless of what prefix, suffix, and 
extension is entered. 

- Late yesterday, I created a similar where statement (with a lot more 
comparison logics) and tested in interactive sql.  For some reason, the sql 
engine doesn't want to use the existing logic, it did a sequential search 
instead.  As Joe has indicated before, that darn sql engine  is a black box 
that we can't have full control of.

 


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On 
Behalf Of Hauser, Birgitta
Sent: Friday, June 02, 2006 9:29 AM
To: 'rpg400-l@xxxxxxxxxxxx'
Subject: RE: Constructing Sql to position phone number that breakup in 
areacode, prefix, suffix and extension

Hi Lim,
 

Your sql statement will only result in the phone number entered.  The 
subfile display requires listing phone numbers starting from the 
InputNbr.

 
Then try the following: (But be aware, I'm not sure about the performance)
 
Select   AreaCode, Prefix, Suffix, Extenstion
   From  TelefoneFile
   Where         AreaCode  >= Substr(:InputNbr,  1, 3)
         or      AreaCode   = Substr(:InputNbr,  1, 3) 
             and Prefix    >= Substr(:InputNbr,  4, 3)
         or      AreaCode   = Substr(:InputNbr,  1, 3) 
             and Prefix     = Substr(:InputNbr,  4, 3) 
             and Suffix    >= Substr(:InputNbr,  7, 4)
         or      AreaCode   = Substr(:InputNbr,  1, 3) 
             and Prefix     = Substr(:InputNbr,  4, 3) 
             and Suffix     = Substr(:InputNbr,  7, 4)
             And Extention >= Substr(:InputNbr, 10, 3)

 

 

Mit freundlichen Grüßen

 

i.A. Birgitta Hauser

 

LUNZER + PARTNER GMBH

Consulting | Software | Service

Carl-Zeiss-Straße 1

63755 Alzenau

 

Tel:         + 49 6023 951-255

Fax:        + 49 6023 951-111

Internet.  www.lp-gmbh.com <http://www.lp-gmbh.com/> 

              www.rpg-schulung.de <http://www.rpg-schulung.de> 

 

--------------------------------------------------------

Handelsregister: Aschaffenburg HRB 4720

Gerichtsstand und Erfüllungsort Alzenau

Ust-IdNr.: DE132093146

Geschäftsführer: Rudolf Gerbert

---------------------------------------------------------

 

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list To 
post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or 
change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at 
http://archive.midrange.com/rpg400-l.