I need to write sql to work with a cobol group level

Joe Gennero · New User Joined: 13 Feb 2009 Posts: 1 Location: 48092

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:

AT_NPA_NO CHAR(03)
AT_NXX_NO CHAR(03)
AT_STATION_NO CHAR(04)

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:
01 ws-phone-low.
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
SELECT AT_NPA_NO
,AT_NXX_NO
,AT_STATION_ID
FROM PHONET00
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')
AND (AT_NPA_NO||AT_NXX_NO||AT_STATION_NO
BETWEEN :WS-PHONE-LOW
AND :WS-PHONE-HIGH)
ORDER BY AT_NPA_NO DESC
,AT_NXX_NO DESC
,AT_BLC_ID 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?

CICS Guy · Posted: Fri Feb 13, 2009 2:08 am

Why:
AND (AT_NPA_NO||AT_NXX_NO||AT_STATION_NO
BETWEEN :WS-PHONE-LOW
AND :WS-PHONE-HIGH)
?
Isn't the fourth condition already taken care of by the previous three?

ykishor · New User Joined: 11 Aug 2007 Posts: 24 Location: my pc

I guess I have understood the requirements clearly: u r using DB2 and COBOL. I guess this will be much faster.

Use a cursor to fetch rows from the table and pass it on to a COBOL variable. Like
AT_NPA_NO to :WS-FIRST
AT_NXX_NO CHAR to :WS-SECOND
AT_STATION_NO CHAR :WS-THIRD

Declare WS-FIRST,WS-SECOND,WS-THIRD under a level 1 variable, so the leve1 variable will have the concatenated form of the phone no.

Now as you fetch the rows from the table pass it to the level 1 variable and compare it using ur condition. write the O/P accordingly.