8 replies to this topic

[Vol Yip]

All,

I am using 4.6r500 running on NT / Oracle. I am encountering a strange situation.

I have an update function and at the default logic, I use SEL-RANGE to select records from a table which has a few keys. The first key has a length of 6.

I pass the keys from a screen using @$COM variables.

In the update, I set

$$RANGE = $ENDKEY1 + $ENDKEY2 + $ENDKEY3

where $ENDKEY1 / 2 / 3 receive from @$COM2/3/4

At my development machine, everything works fine and the SEL-RANGE($$RANGE) actully return what I want.

However, when I implement this in the LIVE machine, the SEL-RANGE($$RANGE) returns everything no matter what my keys are.

I have to change

$$RANGE = $ENDKEY1(1,6) + $ENDKEY2 + $ENDKEY3 in order to make the SEL-RANGE($$RANGE) works properly.

Do you know why is that? Why the same function works in one machine but not the other?

The two machines have identical setup, i.e., same PROIV kernel.

Any idea?

Thx.

[Vol Yip]

Thx. I did try this.

I do not know if this is the design of SEL-RANGE.

For example, I have a file with two keys. Key1 (length=2) and Key2 (length=3)

And I have the following data

Key1 Key2
------ ------
A 2
A 3
A 4
A1 2
A1 3
A1 4
B 2
B 3

If I set

Key1 = 'A'
Key 2 = '2'
$ENDKEY = '3'
$RANGE = Key1 + $ENDKEY
SEL-RANGE($$RANGE) will return 8 records...

I was expecting all records with KEY1 = 'A' and KEY2 = '2' and '3' only....

However, if $RANGE = Key1(1,2) + $ENDKEY will return what I expect...

[Richard Bassett]

Yup, SEL-RANGE is a mess.

You must always fully pad every wholly-specified (ie. fixed) part of the key with spaces.
Except of course the least-significant part you are specifying (ie. the rightmost key variable in the concatenated key).

AFAIAA, SQL_AGGREGATE_KEY hasn't worked since V4.0 - when it was apparently quietly abandoned (it was frequently a performance problem).

[Rob Donovan]

Also....

Dont do this, as it does not work....

SEL-RANGE($ENDKEY1(1,6) + $ENDKEY2(1,4) + $ENDKEY3(1,4))

You must put the variables into one variable and use that. You cannot string the vars together in the SEL-RANGE() statement.

I reported this as a bug a few years back, and suprise suprise... its not a bug.... They said that ProIV does not support that.

Strange, because everywhere else in ProIV you can do that sort of thing

Rob D.

[Vol Yip]

Thx Rob.

By the way, what do you mean it is a perforamance issue for SQL_AGGREGATE_KEY? What problem is it? Shall I take out this from my PRO4.INI?

Regards,

Vol

[Richard Bassett]

> By the way, what do you mean it is a performance issue for SQL_AGGREGATE_KEY? What problem is it?
> Shall I take out this from my PRO4.INI?

As I said, I *think* SQL_AGGREGATE_KEY has been obsolete, at least on Unix, since ProIV V4.0.
Thus I *think* it will make no difference whether you set it or not on V4.6.
Test it to your own satisfaction if you decide to remove it though!

I think the performance issue was that the database did not effectively optimize access due to the complex WHERE clause that was required to emulate the semantics (ie. precise set of returned records) that SEL-RANGE provides on ISAM files.
So, I *think* SEL-RANGE *never* has the same general semantics on SQL and ISAM any more.


> You must put the variables into one variable and use that. You cannot string the vars together in the
> SEL-RANGE() statement. I reported this as a bug a few years back, and suprise suprise...
> its not a bug.... They said that ProIV does not support that. Strange, because everywhere else in
> ProIV you can do that sort of thing

Also - don't change the value of the variable you use for SEL-RANGE until the affected LS has finished!
My guess is ProIV doesn't take a copy of the range-end value and so (in some cases anyway?) actually continues to test against the storage used for your variable to determine when end-of-range is reached.

HTH..

[Marlon]

Re SEL-RANGE not working with SEL-RANGE(var1 + var2 etc.), that's not a bug, it's a feature.  PROIV is full of features!

 

We don't use SEL-RANGE any more as we are totally Oracle/SQLServer, we also use FFSQL in the selection logic.