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

Thanks Birgitta, 

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

Example: 
Assuming Phone Master contains:
AreaCode        Prefix  Suffix  Extension
601             221             5555            231333  
601             221             5555            332333  
721             125             3300    
721             125             3400            955663
721             125             3500            221111
. . .

If user enter 601-221-5555 331222, the subfile will display
601             221             5555            332333  
721             125             3300    
721             125             3400            955663
721             125             3500            221111
. . .


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Hauser, Birgitta
 
This SQL statement will not use any access path created over the
physical file, because indexes can only be created over existing columns
and concating several columns together will create a new one.
 
But what about the following solution.
Select   AreaCode, Prefix, Suffix, Extenstion
   From  TelefoneFile
   Where     AreaCode  = Substr(:InputNbr,  1, 3)
         And Prefix    = Substr(:InputNbr,  4, 3)
         And Suffix    = Substr(:InputNbr,  7, 4)
         And Extention = Substr(:InputNbr, 10, 3)