5 replies to this topic

[jcjones]

I have a file with 4 key fields, and several data fields after that. In an LS that just looks up bits of data from various files based on user input, I need to read a record from this file, but I do not know one of the key fields. Is there a way to tell PROIV to read the first record that matches the data I do know?

For example:
Key field 1 : known
Key field 2 : not known
Key field 3 : known
Key field 4 : known
field 5 : not known
field 6 : not known
field 7 : known

How would I handle this in the file Before Read function to tell it to just read the first record that matches fields 1,3,4 and 7, ignoring the fact that I don't know the value for the key field 2? And if this is possible, for future reference, how might I do the same thing if I only knew 1 or 2 of the 4 key fields?

Thanks,
JC Jones

[Rob Donovan]

Hi,

What file type is the file, ProISAM, C-ISAM, Oracle?

Rob

[jcjones]

The file is a ProISAM file.

Since my initial question, I have tried adding an LS to just read through this file with the SEL-ONLY in the default logic for the new LS, having it select all records from the file with the first key. However, it is executing the default logic, and I'm checking the value of key passed in the SEL-ONLY statement to be the correct first key. It does execute the Read No Error function for the file in this LS, but only once and all of the fields that should be read are empty after the read, then it exits without reading another record. I have verified that there are hundreds of records in this file with the requested key. Not sure what it's doing, but I'm still working on it and tracing things out with UMSG statements.

Any help would be appreciated.

Thanks,
JC Jones

[andykay]

JC,

Have you tried to build a SEL-RANGE statement.

Key field 1 : known
Key field 2 : not known
Key field 3 : known
Key field 4 : known
field 5         : not known
field 6         : not known
field 7         : known

KF2 = ''

$$RANGE = KF1 + $UPPER_LIMIT_OF_KF2 + KF3 + KF4
SEL-RANGE($$RANGE)

It's a shame that SET_CURR deosn't works in ProIV, because that is exactly what you need.

Good Luck,

AK

Edited by andykay, 25 May 2006 - 01:15 AM.

[Chris Pepper]

With ProIsam the only way to handle this situation is to select a range of records which contains the records you want to select (to minimise the total number of records read) - then filter out the non matching records in the After Read (or Sort/Select possibly).

If you are using a SEL-RANGE and you do not know the last part(s) of the key (key 4 in this case) you need to set this to an appropriate high-value to ensure all the records are selected. That's because it will stop as soon as it finds a key greater than the value you give it.

A ProIsam file always has a single physical key - although ProIV allows you to split it up into different fields... but these are very different from the columns in a database, they don't have a separate existence on the file. In fact with a ProIsam file you can have alternate definitions of the same file with a different number of fields in the key provided the total number of characters in the key is the same. (I don't advise this if you ever want to go to Oracle - we have lots of files like this and have had to create some complex workarounds!).

So you do never really have separate key fields. The File definition may say K1 len 1, K2 len 3, K3 len 2, K4 len 5 but what ProIsam really stores is KEY len 11, and when you retreive the record ProIV when it deblocks the record says K1 = KEY(1,1) K2 = KEY(2,4) K3 = KEY(5,6) K4 = (7,11).

With a ProIsam file think of it like using a tabbed Telephone Directory where the first letter of the Surname is K1, the next 2 are K2, the next couple are K3 etc. That's where there is a cut out to show you where each letter starts. So if you know the first part of the key you can go straight to the first Name that starts with, say, F. But if you don't know the second part you have to keep reading through checking for the parts you don't know.

Edited by Chris Pepper, 25 May 2006 - 08:04 AM.

[Joseph Bove]

JC Jones,

I just wanted to add the following about the SEL-RANGE suggestion. Even though you can use it limit what you return, you will still get records you have to throw out.

In the following example, assume that all keys are 1 char

Key 1 = A
Key 2 = ''
Key 3 = I
Key 4 = D

$RANGE = 'AZID'
SEL-RANGE($RANGE)

You will get records like

ABBA
ACES
ARID
etc

All the sel-range does is says return all records between A_ID and AZID. This will not return records exclusively like: ACID, ARID, etc.

Regards,

Joseph