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

Just jotting something down...  look it over and see if it will work...
(not sure about performance either)


Select   AreaCode, Prefix, Suffix, Extenstion
   From  TelefoneFile

*** This will get you 614-555-5555 ext 5555 or whatever...
   Where (AreaCode   = Substr(:InputNbr,  1, 3) And
          Prefix    >= Substr(:InputNbr,  4, 3) And
          Suffix    >= Substr(:InputNbr,  7, 4) And
          Extention >= Substr(:InputNbr, 10, 3))
      OR
*** Then this should get every other number greater than the area code.
         (AreaCode  >  Substr(:InputNbr,  1, 3) And

Order by AreaCode, Prefix, Suffix, Extention

I suspect that if 614-555-5555 was entered, that it should be the first
record.


Michael Schutte



                                                                           
             "Hauser,                                                      
             Birgitta"                                                     
             <Birgitta.Hauser@                                          To 
             LP-GMBH.com>              "'rpg400-l@xxxxxxxxxxxx'"           
             Sent by:                  <rpg400-l@xxxxxxxxxxxx>             
             rpg400-l-bounces@                                          cc 
             midrange.com                                                  
                                                                   Subject 
                                       RE: Constructing Sql to position    
             06/02/2006 10:28          phone number that breakup in        
             AM                        areacode, prefix, suffix and        
                                       extension                           
                                                                           
             Please respond to                                             
              RPG programming                                              
              on the AS400 /                                               
                  iSeries                                                  
             <rpg400-l@midrang                                             
                  e.com>                                                   
                                                                           
                                                                           




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.