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

Thanks Michael.

Your sql statement appears to be working great.  Sql engine uses an existing 
logical (Not the one that I expected) and there is not speed problem.

-----Original Message-----
From: Michael_Schutte@xxxxxxxxxxxx [mailto:Michael_Schutte@xxxxxxxxxxxx] 
Sent: Friday, June 02, 2006 10:21 AM
To: Lim Hock-Chai
Cc: Hauser, Birgitta
Subject: Fw: Constructing Sql to position phone number that breakup in 
areacode, prefix, suffix and extension



I'm sending you this privately.  If it works, you may post it to the group.
I was just trying to cut down on the noise.

InputNbr = 614-555-5555


   Where (AreaCode   = Substr(:InputNbr,  1, 3) And
          Prefix     = Substr(:InputNbr,  4, 3) And
          Suffix     = Substr(:InputNbr,  7, 4) And
          Extension >= Substr(:InputNbr, 10, 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))
      OR
         (AreaCode   > Substr(:InputNbr,  1, 3))

If Record has 614-555-5555 ext 1111, first where statement will select it.
If Record has 614-555-5556 ext xxxx, second statement will select it.
If Record has 614-556-xxxx ext xxxx, third statement will select it.
If record has 714-xxx-xxxx ext xxxx, last statement will select it.


I used xxx and xxxx to mean "anything enter"



Michael Schutte



                                                                           
             Michael                                                       
             Schutte/Bob Evans                                             
                                                                        To 
             06/02/2006 10:55          "Lim Hock-Chai"                     
             AM                        <Lim.Hock-Chai@xxxxxxxxxxxxxxx>     
                                                                        cc 
                                                                           
                                                                   Subject 
                                       Fw: Constructing Sql to position    
                                       phone number that breakup in        
                                       areacode, prefix, suffix and        
                                       extension                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           



Sorry this won't work either.

Michael Schutte

----- Forwarded by Michael Schutte/Bob Evans on 06/02/2006 10:55 AM -----
                                                                           
             Michael                                                       
             Schutte/Bob Evans                                             
                                                                        To 
             06/02/2006 10:50          RPG programming on the AS400 /      
             AM                        iSeries <rpg400-l@xxxxxxxxxxxx>     
                                                                        cc 
                                                                           
                                                                   Subject 
                                       RE: Constructing Sql to position    
                                       phone number that breakup in        
                                       areacode, prefix, suffix and        
                                       extension(Document link: Michael    
                                       Schutte)                            
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           



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.