× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.






As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.