Re: STD phone code lookup

Tweetie Pooh wrote:
>
> Another one of those problems I don't know how to ask easily.
>
> I need to locate the operator for a phone number.
>
> The operator is linked to the first digits of the phone number but the number
> of digits varies from number to number possibly from 3 to 7 digits.
>
> I can control the data and the lookup tables. Anyone done something like
> this?
>
> I can handle things programatically in simple single record lookups but what
> about user generated reports.
>
> The longest match on a number would be what we need. ie if we get a match at
> 5 digits then we don't need to look up 4 or 3. Record numbers are not great,
> about 250 000 per annum and most processing is batch/automatted but reports
> and status screens are updated regurly (every minute) so this may need to
> work quite fast.

  It may be something like this:

SELECT C.phone_num, O.oper_code
FROM calls C

   , operators O
WHERE C.phone_num LIKE O.oper_code || '%'   AND NOT EXISTS

     (SELECT * FROM operators Y
      WHERE C.phone_num LIKE Y.oper_code || '%'
        AND O.oper_code < Y.oper_code)

Valery Yourinsky

-- 
Oracle8 Certified DBA
Moscow, Russia