Re: Constructing Sql to position phone number that breakup in are acode, prefix, suffix and extension

Hi,
 

Select areacode || prefix || suffix || extension from telephonefile where
areacode || prefix || suffix || extension = :phonenumber

 
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)
 
For a dynamical sort, you could add an order by clause as follows:
 
Order By Case When :Sort = 1 Then Equipement Else NULL End,
         Case When :Sort = 2 Then AreaCode Else NULL End,
         Case When :Sort = 2 Then Prefix Else NULL End,
         Case When :Sort = 2 Then Suffix Else NULL End,
         Case When :Sort = 2 Then Extention Else Null End
 
:Sort is a host variable that holds the sequence the user selected.
 
With this statement an existing access path with the key fields AreaCode,
Prefix, Suffix and Extention can be used.
 
By the way, and SQL view won't help. Because it has no key either. 

 

 

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

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