When I enter the following command in the system editor:

run rlist "LIST LABOR WITH PAY_PERIOD=9814'", 1

It gives me the message "WARNING: Too much information returned; Truncating…", which is fine, since there are 2,146 rows that match the criteria.

Then I enter the command:

run rlist "LIST LABOR WITH PAY_PERIOD=9812' ", 1

And again, it truncates, as there are 2,122 records that match.

However, when I enter the command:

run rlist "LIST LABOR WITH PAY_PERIOD=9814' OR WITH PAY_PERIOD=9812' ", 1

It returns 43 records! Why? There *should* be 4,268 records that match that criteria. Can anyone explain this?

Is there an index on the PAY_PERIOD field? I've had problems where specifying a single value to search for works fine but two or more doesn't because the indexes were out of synch.

dbakke@srpcs.com

SRP Computer Solutions

Yes, there is a BTree index on the PAY_PERIOD field. Suspecting that it could be corrupted, I rebuilt the index. I now get 44 rows back instead of 43 (still expecting 4,268)!

Well, here's the next little frustration in my search for an answer to my querying problem. I tripped over the Btree.Extract subroutine in the help files and thought that it would be the answer.

I have a PAY_HISTORY table with about 23,000 records. The key is EMP_NO*PAY_PERIOD*COUNT. Here are the columns and the criteria for the query:

  COMPANY_CODE=C97

  PAY_PERIOD=9814 or 9812

  PAY_CODES=1, 1A, or 1B

I went into Database manager and created BTree indexes for all three columns. Then I used the following code, copied and adapted from the BTree help topic:

  Table=PAY_HISTORY"

  OPEN "DICT.":Table TO @DICT ELSE

      IOStatus=OPEN failed on file DICT.":Table

      RETURN

  END

  Search_Criteria =PAY_PERIOD":@VM:"9814":@VM:"9812":@FM

  Search_Criteria := "CO_SUBCO":@VM:"C79":@FM

  Search_Criteria := "PAY_CLASS":@VM:"1":@VM:"1A":@VM:"1B":@FM

  KeyList="

  Option ="

  Flag   ="

  Btree.Extract( Search_Criteria, Table, @DICT, KeyList, Option, Flag)

When I run this code, Keylist is empty. Flag is 0, so the Btree.Extract was successful, only it doesn't appear to return any keys. If I only use one column in the Search_Criteria, (and it doesn't matter which column,) it returns more keys than the KeyList variable can contain. But if I try two or more columns, it never returns any keys. (And yes, I have verified that there are actually records that satisfy the criteria.)

I've tried RList, the Reduce subroutine and now Btree.Extract and it just doesn't seem like OI is going to let me perform this query! I'm about to go postal! Anyone have any suggestions? (About the program, not about going postal.)

Mark Petereit

If you remove all the indexes and try the RList statement, will it return the correct keys?

If so, try removing all indexes from the table (including the !filename) and recreate the indexes.

Another possibility may be a null record in the file, or record(s) with system delimiters in the key field.

Jeff

After removing the indexes the R/List command now works!

In your message you wrote, "try removing all indexes from the table (including the !filename) and recreate the indexes".

Can you elaborate on the "(including the !filename)" bit? I'm not following you there.

After removing the indexes the R/List command now works!

That's good news (I think) - it looks like the problem is related to the indexes.

Can you elaborate on the "(including the !filename)" bit? I'm not following you there.

From the Database Manager, when you choose to remove all indexes from a file, sometimes the !filename (the file that holds the indexes/pending transactions) still remains - and sometimes not. It has been my experience that it is good to make sure that the file gets deleted when index problems exsist. You can go the add file part of the Database Manager, and see if the filename (with the ! in front of it) still exists for the file you removed the indexes from. If it does, delete it from the System Editor command line (use DELETE_TABLE) before recreating any indexes for the file. Then go back to the DM, and add your indexes back to the file - a new !filename will be created.

Jeff

Thank you for that explanation. I did verify that the !filename table was deleted when I removed the indexes. However, when I add the indexes again, I still get the same problem as before. You mentioned that it could be caused by a null record or by system variables in keys. Since this table was created by importing an ASCII data file provided by my customer, I suppose it's possible.

Is there a simple way to test for these conditions?

There is no built in function to check for nulls/delimiters, but writing a program to do it isn't that hard. Select the entire file, loop through each record, check the keys for null or delimiters. After checking the key, you could read the record and write the good ones out to a temporary file. (I think delimiters in a key may give you incorrect results when using READNEXT - so the read/write assures that the record exists).

Jeff

Mark,

There are snippets of code here for scanning keys for lower case letters. You could easily adapt the scan to look for system delimiters instead.

Cameron Purdy

Revelation Software

First off, thank you so much for your assistance! I'm new at programming in OI and I'm finding the support here unparalleled.

OK. I removed all indexes, and verified that the !filename table was deleted as well. I verified that there are no system delimiters in any of the keys and that there are no null records. I then rebuilt the Btree indexes.

Everythough should be fine now, but the Btree.extract example I used a few posts back still doesn't work. Again, I'm examining 3 columns. When I restrict the Btree.extract criteria to one column at a time, it works fine, but when I try to use two or more columns, it reports successful completion, but never returns any keys.

Any more suggestions?

Mark,

Could it have anything to do with precedence?

Cameron Purdy

Revelation Software

Search_Criteria=PAY_PERIOD":@VM:"9814":@VM:"9812":@FM Search_Criteria := "CO_SUBCO":@VM:"C79":@FM Search_Criteria := "PAY_CLASS":@VM:"1":@VM:"1A":@VM:"1B":@FM

Mark,

When all else fails - lets go back to the source. According to the BTree.Extract documentation, multiple values for one column default to an 'OR' comparison. Multiple columns default to an 'AND' comparison.

So, the above criteria would equate to:

(Pay_Period=9814 or 9812) AND (CO_SUBCO=C79) AND (PAY_CLASS=1 or 1A or 1B)

Is this the way you actually want the records to be selected? Are you sure there are records that meet the criteria?

If so . . . do you get any results if you use the RList select with the same criteria? How about sequential select statements, reducing the list each time through? (select file with pay_period=xxx, select file with co_subco=xxx, select file with pay_class=xxx). Just another thought - maybe we can stumble on a clue.

At this point, I can't think of anything else that can be causing the indexes to fail. You've rebuilt, checked for delimiters/null records - those are the first things that come to mind when an index returns 'wrong' results.

Jeff

Firstly if you use run rlist "LIST LABOR WITH PAY_PERIOD=9814' '9812' ", 1 what do you get?

Secondly if you declare function get_status then after the btree.extract do a A=Get_Status(B), what do A and B return?

amcauley@sprezzatura.com

Sprezzatura Ltd

World Leaders in all things RevSoft

With all indexes of the PAY_HISTORY table removed:

* Code Sample 1 *

* Run from the command line in *

* System Editor *

run rlist "LIST PAY_HISTORY ( WITH PAY_PERIOD=9812' OR WITH PAY_PERIOD=9814' ) AND WITH CO_SUBCO=C79' AND ( WITH PAY_CLASS=1' OR WITH PAY_CLASS=1A' OR WITH PAY_CLASS=1B' ) ", 1

* End Code *

…produces "Request successfully executed. 669 row(s) processed."

* Code Sample 2 *

* Run from the command line in *

* System Editor *

run rlist "LIST PAY_HISTORY ( WITH PAY_PERIOD=9812' OR '9814' ) AND WITH CO_SUBCO=C79' AND ( WITH PAY_CLASS=1' OR '1A' OR '1B' ) ", 1

* End Code *

…produces "Request successfully executed. 669 row(s) processed."

* Code Sample 3 *

* Run from a Stored Procedure *

SortList =EMP_NO"

Flag ="

Table =PAY_HISTORY"

LABORCursor=0

Mode =0

Script=WITH {PAY_PERIOD} EQ '9812' OR WITH {PAY_PERIOD} EQ '9814'"

Reduce( Script, SortList, Mode, Table, PAYHISTCursor, Flag )

If Flag Else

  IOStatus=REDUCE failed on file " : Table : "|in subroutine OpenFiles"

  Return

End

Mode=2

Script=WITH {CO_SUBCO} EQ 'C79'"

Reduce( Script, SortList, Mode, Table, PAYHISTCursor, Flag )

If Flag Else

  IOStatus=REDUCE failed on file " : Table : "|in subroutine OpenFiles"

  Return

End

Script =WITH {PAY_CLASS} EQ '1' "

Script := "OR WITH {PAY_CLASS} EQ '1A' "

Script := "OR WITH {PAY_CLASS} EQ '1B'"

Reduce( Script, SortList, Mode, Table, PAYHISTCursor, Flag )

If Flag Else

  IOStatus=REDUCE failed on file " : Table : "|in subroutine OpenFiles"

  Return

End

Select Table By SortList Using PAYHISTCursor Else

  IOStatus=SELECT failed on file " : Table : "|in subroutine OpenFiles"

  Return

End

TOTALCOUNT=@RECCOUNT

DEBUG

* End Code *

…TOTALCOUNT=2450 (very interesting!)

Next, I added BTree indexes to the PAY_PERIOD, PAY_CLASS and CO_SUBCO fields:

* Code Sample 4 *

* Run from a Stored Procedure *

table=PAY_HISTORY"

Open "DICT.":table To @DICT Else

   RetVal=Set_FSError()

   Return

End

search_criteria =PAY_PERIOD":@VM:"9812":@VM:"9814":@FM

search_criteria := "CO_SUBCO" :@VM:"C79":@FM

search_criteria := "PAY_CLASS" :@VM:"1":@VM:"1A":@VM:"1B":@FM

keylist="

option="

flag="

Btree.Extract(search_criteria, table, @DICT, keylist, option, flag)

debug

* End Code *

…keylist=', flag=0'

Please point at the stupid mistake I'm making and restore my faith in OI. Otherwise, I'm going to end up pulling out what little hair I have left and go back to Visual Basic! (EEeeewwww!)

Please point at the stupid mistake I'm making and restore my faith in OI. Otherwise, I'm going to end up pulling out what little hair I have left and go back to Visual Basic! (EEeeewwww!)

Mark,

Looking at your samples, I've got a couple comments. In sample 3, I believe the initial Mode value should be 1 instead of 0 (0 indicates theses are new reduction criteria for an existing cursor). That may explain (or may not the odd results in that example.

As for example 4 - I cut/pasted your code into our system (3.61), and replaced the file/field names with one of my tables - and it seems to work fine. At least indicating the syntax and the Btree.Extract routine is working as it should (here anyway). It also seems to point to (once again), something going wrong with your file.

Could you try this - create a new table with similar fields to the one you are using (don't copy anything - start from scratch). Then create a few test records. Then try the same tests - build the indexes and see what happens.

If that works (go one step at a time), copy all the records from the old table to the new - and repeat.

You get the idea - I would try to isolate the problem as much as possible. Is it the table, the data, the indexes???

Good Luck - don't loose your hair just yet . . .

Mark,

I've never used REDUCE with Mode=2 before, so this is a guess. I'm wondering if the implied "AND" only works against "WITH {PAY_CLASS} EQ '1' " and the remaing "OR WITH…" are treated separately. This would certainly give you a larger result as what you experienced. So perhaps you need to surround everything with parenthesis as well.

Regarding your code for Btree.Extract, it looked good to me. What happens (now that you have indexes back on) when you run the first two RList statements? BTW, are the 669 results the correct ones?

dbakke@srpcs.com

SRP Computer Solutions

Mark,

This thread has grown quite large, and I haven't read everything, so I don't know if I have any new information, and I don't know if it fits with the exact symptoms. Anyway,

At least in some versions of OI there can be problems if your station id (check your @STATION system variable) has fewer than 6 (7?) letters and contains an "R". I don't remember now what happened last time I came across this, but at least this time it affected search. My colleague just got these bizarre results where e.g RList returned the beginning of the list, and a second, identical RList returned the rest of the list. From then it went in a cycle. My colleague runs OI 3.3. - There is a slight chance we're mistaken and the cause of his problems is something else, but I don't think so.

(The last time I met this problem I think it was Cameron who told me about the "R" problem, but I cannot find his posting now.)

- Oystein -

The thread's pretty long, and I haven't bothered to trace through it all to see if anyone suggested this, but…..I wonder if it's the old ARev thing with keylengths where the length of the key 0 has to be about 4 or 5 characters greater than the length of the largest key?

akaplan@sprezzatura.com

Sprezzatura, Inc.

Actually, the program's not as easy as it sounds. Finding @VM's can be difficult because the system might return them as part of the MV position indicator. In the ReadNext command you should really

ReadNext Key, MoreKey Then/Else

and check to see of something is in MoreKey. If so, that's the @VM and the key would be Key:@VM:MoreKey.

For @FM, the system will always return them as seperate keys. So, if I have a key in my system 123:@FM:456, the readnext will give me 123 then the next time will give me 456. If both of these are valid keys, you'll never report back an error. If only one is, you'll find yourself very confused.

What you need to do is store off the previous key and check to see if PKey:@FM:Key exists.

akaplan@sprezzatura.com

Sprezzatura, Inc.

I'd be interested in seeing what the results are if you just issue one reduce command. Reduce doesn't actually do anything until Select is called. All it does it set some memory pointers.

Also, when working with reduce/select on non-indexed fields, most of the time, the results of @RECCOUNT are meaningless. The only time @RECCOUNT gets updated is when the select is resolved. Generally, reduce/selects are latent.

akaplan@sprezzatura.com

Sprezzatura, Inc.

I just started getting this problem. It turns out that the problem occurs when the Sort By field is indexed

If ReduceScript Else Goto End.Pgm

For x=1 to 8

  CLEARSELECT x

Next

@Reccount=0

SortList =EMP_NO' * to correct my error, i sorted on @ID, but you just create an other symbolic field and sort on that ….

FileName =SelCriteriaFileName

ReduceOk ='

Reduce(ReduceScript, SortList, '1', FileName, Cursorvar, ReduceOk)

If ReduceOk Else

 Msg('', 'Reduce Failed, Program will not run')

 GoTo End.Pgm

End

Select FileName By SortList Using Cursorvar Else

 Msg('', 'Select Failed, Program will not run')

 GoTo End.Pgm

End

If @Reccount=0 Then

 Msg('', 'No records selected, Program will not run')

 GoTo End.Pgm

End

Tom,

I just started getting this problem. It turns out that the problem occurs when the Sort By field is indexed

I (my app and all my clients) have no problem sorting on indexed symbolics. But I use Rlist, not Reduce/Select.

(If the sort field is multivalue, one can, for obvious reasons, get more hits than expected. But if it's indexed or not shouldn't matter.)

If it runs ok when the sort field isn't indexed, there might be something wrong with the index. Can you somehow inspect the index and check what's there?

- Oystein -

View this thread on the forum...