I need to write a SQL statement that will give me a results set that has rows that is >= to a phone number and <= to another phone number. The only problem my database defines each part of the phone number separately. So my phone number looks like this in DB2:
I have an index made up of these three columns but the sql I’m using to get this results set isn’t picking it up. I don’t have a separate column that has all 10 digit of the phone number in one column.
In my working storage section I’m storing 2 phone numbers (a low and a high number) as follows:
05 ws-nxx-low pic x(03)
05 ws-npa-low pic x(03)
05 ws-station-low pic x(04)
Right now my SQL look like the following
WHERE (AT_NPA_NO BETWEEN :WS-NPA-LOW AND :WS-NPA-HIGH)
AND (AT_NXX_NO BETWEEN '000' AND '999')
AND (AT_STATION_NO BETWEEN '0000' AND '9999')
ORDER BY AT_NPA_NO DESC
This is working but it is using a ton of CPU. It's a large database and using the incorrect key is causing the excessive CPU time.
I know I've seen a formula where the where clause would be split up into multiple parts. I'm just not sure how I would do that. Any idea. We are thinking of adding another column which has the 10 digit phone number but I'm sure there is a way to do it.
Has anyone run into something like this where you have to search on 3 column but need to think of 3 columns as 1 for the select?