Jump to content


Click the link below to see the new game I'm developing!


Photo
- - - - -

SEL-RANGE behaivours differently


8 replies to this topic

#1 Vol Yip

Vol Yip

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 393 posts
  • Gender:Male
  • Location:Hong Kong

Posted 11 April 2003 - 03:22 PM

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.

#2 Guest_Joseph V.G. Orendain_*

Guest_Joseph V.G. Orendain_*
  • Guests

Posted 11 April 2003 - 06:54 PM

No idea why its not behaving the same on both environments maybe there is a setting not correctly set on the other machine (related to sel-range command). Normally when I used SEL-RANGE command, I always specify the total length (ie ITEM(1,30), etc) just to make sure i get the whole string value not with stripped spaces. :lol:

#3 Guest_Glen Thompson_*

Guest_Glen Thompson_*
  • Guests

Posted 12 April 2003 - 10:57 AM

If i remember rightly you need to set the environment variable SQL_AGGREGATE_KEY to allow SEL-RANGE to work in the same way for SQL as it does for ISAM databases otherwise it uses some bizarre selection method based upon the individual key elements to return the records...

#4 Vol Yip

Vol Yip

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 393 posts
  • Gender:Male
  • Location:Hong Kong

Posted 12 April 2003 - 02:18 PM

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...

#5 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 707 posts
  • Gender:Not Telling
  • Location:Rural France

Posted 14 April 2003 - 08:49 AM

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).
Nothing's as simple as you think

#6 Rob Donovan

Rob Donovan

    rob@proivrc.com

  • Admin
  • 1,651 posts
  • Gender:Male
  • Location:Spain

Posted 14 April 2003 - 09:24 AM

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 :huh:

Rob D.

#7 Vol Yip

Vol Yip

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 393 posts
  • Gender:Male
  • Location:Hong Kong

Posted 14 April 2003 - 09:30 AM

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

#8 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 707 posts
  • Gender:Not Telling
  • Location:Rural France

Posted 14 April 2003 - 05:18 PM

> 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.. :huh:
Nothing's as simple as you think

#9 Marlon

Marlon

    Member

  • Members
  • PipPip
  • 49 posts
  • Gender:Not Telling
  • Location:London

Posted 05 November 2020 - 10:09 AM

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.


This is your captain speaking. We may experience some slight turbulance and then...explode.




Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Click the link below to see the new game I'm developing!