Using OI 3.7.0, I came a-cropper when attempting to use RList to select row keys from an application's transaction log.

Scenario :

A multi-select popup populated with the keys of an instrument inventory file

The operator selects those instrument IDs for the purpose of reporting the instruments' transactions during a period of time.

The instrument keys are then assembled into variable INST_LIST

With S/List, the query statement reads:

---

Slist('LIST CAL_LOG BY CAL_INST BY CAL_DATE (WITH CAL_INST=:INST_LIST:') AND WITH CAL_DATE GE ':BDATE1:' AND WITH CAL_DATE LE ':EDATE1:' …..')

---

Everything runs well, but I discovered that using the same selection criteria with RList, using parentheses to establish selection hierarchy (The INST_LIST selection clause, then the date selection clause), RList('SELECT CAL_LOG ….) bombs out by ignoring the date selection clause all together.

S/List works fine with the parentheses, RList does not. Verry interesting.

I also learned while researching this topic that 'BETWEEN' works better than GE and LE. Does this mean that the selection is faster?

Mark

Mark-

Yep- Parentheses don't work in RList in OI.

Yep Yep- Between has always been faster that ]= and ⇐. Arev and OI.

Mike Ruane

WinWin Solutions Inc.

WWW.WinWinSol.Com

Mark

Ditto what Mike said. Not only do parentha .. brackets not work, I have found that three or more WITH statements don't work either. Also try an ID-SUPP by mistake and see what happens…

So we developed the following routine which we call instead of RLIST. It basically calls RList multiple times after breaking the command down into single WITH statements. We have been using it for some time so it seems to work for us.

COMPILE SUBROUTINE MY_RLIST(CMD, TARGET, P1, P2, P3)

Declare Function Utility, Msg

Declare Subroutine Msg

*———————————————————————

XCRLF =CHAR(10) : CHAR(13)

old=Utility("CURSOR", 'H')

mess='

mess=Please wait … Sifting Selections' : XCRLF

mess:= 'This may take a few moments.'

mess=U' ;* Message displayed but doesn't wait for a response

Msgup=Msg(@window, mess)

SWAP 'ID-SUPP' WITH IN CMD ;* SWAP '(' WITH IN CMD

SWAP ')' WITH IN CMD BY.POS=INDEX(CMD, 'BY', 1) ;* 32 102 86 16 WITH.POS=INDEX(CMD, 'WITH', 1) ;* 59 32 17 43 IF WITH.POS=0 THEN WITH.POS=LEN(CMD) + 1 IF BY.POS=0 THEN BY.POS=LEN(CMD) + 1 IF BY.POS )

FOR I=1 TO NO.WITHS + 1

IF I <= NO.WITHS THEN

	CALL IES_STATUP(PARENT, 1, @RECCOUNT)

	CALL IES_STATUP(PARENT, 3, 'Sifting - Level ' : I)

	CALL RLIST(CMD.CLAUSE : ' ' : CMD.WITH.CLAUSE[i], 5, '', '', '')

END ELSE

	CALL IES_STATUP(PARENT, 3, 'Sorting Rows...')

	CALL IES_STATUP(PARENT, 1, @RECCOUNT)

	CALL RLIST(CMD.CLAUSE : ' ' : CMD.BY.CLAUSE, 5, '','','')

END

NEXT I

CALL IES_STATUP(PARENT, 3, 'Processing…')

CALL IES_STATUP(@window, 1 , @RECCOUNT)

old=Utility("CURSOR", 'H')

Msg(Parent, Msgup) ;* Take down the message

RETURN 0

This is all slightly confusing as SLIST doesn't do the selection logic itself, it parses it out and calls RLIST with a SELECT. The only thing we can assume is that the parsing on LISTs in R/LIST is flawed whilst the parsing on SELECTS is not.

The Sprezzatura Group

World Leaders in all things RevSoft

Mike ..

It used to be the case that BETWEEN excluded the Start / End values. Betwen 20 and 30 used to exclude 20 and 30. FROM .. TO was the inclusive version. Is that still true? It was true in AREV 2.x.

Don Miller

C3 Inc.

"This is all slightly confusing as SLIST doesn't do the selection logic itself, it parses it out and calls RLIST with a SELECT. The only thing we can assume is that the parsing on LISTs in R/LIST is flawed whilst the parsing on SELECTS is not.

The Sprezzatura Group

World Leaders in all things RevSoft"

Sounds like it. I don't use RList LIST – only Rlist SELECT. Using RList SELECT with parentheses fails, but using the same selection criteria with SList LIST works like a charm.

BTW, does S\List2 now support SAVELIST? If so, does it save it with the same header format as RList?

Well..I was almost right..

Consider the FOLLOWING:

LIST ONLY PATIENT BETWEEN "A" AND "T"

Page 1 13:48:08 01 JUN 2000

Key…………

BUCT

BUSM

CLEC

FORA

FRAA

GRAJ

HARE

HOOJ

JACB

JEFA

JOHE

KENN

LINJ

MONR

NIXG

ROOM

16 Records Processed

.. note that there is no entry for T's

.. now..

LIST ONLY PATIENT BETWEEN "B" AND "TZ"

Page 1 13:48:23 01 JUN 2000

Key…………

BUCT

BUSM

CLEC

FORA

FRAA

GRAJ

HARE

HOOJ

JACB

JEFA

JOHE

KENN

LINJ

MONR

NIXG

ROOM

TAFR

TRUE

TYLJ

19 Records Processed

If you use GE and LE you may get different results if you're not careful.

Don Miller

C3 Inc.

Mike,

] Parentheses don't work in RList in OI.

They don't??? What is it that fails? Parsing or execution?

Parsing will fail with the "(WITH is an invalid dictionary item" message unless you make sure there's a space between "(" and "WITH" in your Rlist statement.

But I have never noticed execution to fail. Parentheses don't cause an erroneous result list to be returned if that's what you mean. Or do you know something I don't?

- Oystein -

Don,

You cannot expect BETWEEN "A" AND "T" to retrieve values greater than "T". "TAFR", "TRUE", and "TYLJ" are all greater than "T".

- Oystein -

Yes…?

After writing a brilliant opus that is now lost forever due to the author's inadvertant failure to enter his name in the proper place, nothing remains but the mockery of an unforgiving executable.

So,

The original posting of this thread was that the RList SELECT statement parsed correctly, but executed improperly when

1)The RList SELECT statement had multiple selection clauses using parentheses to establish hierarchical selection.

And

2) A variable composed of multiple row keys is used within the parentheses.

If variable INST_LIST includes multiple key values (say, "A-11" "A-12" "A-20") and is used within parentheses in a RList SELECT statement, and is then followed by date range selection clause, the date range clause is ignored.

RList('SELECT INSTRUMENT_FILE ( WITH TRANS1 - ':INST_LIST:' ) AND WITH TRANS2 GE ':BDATE:' AND WITH TRANS2 LE ':EDATE:' …)

(and I promise to use 'BETWEEN' from now on)

The RList statement parses correctly, but executes improperly because rows outside the date range are included in the resolved select list.

The identical set of selction criteria used in a S/List LIST statement resolves perfectly.

The problem only occurs (as far as I can tell) when a variable composed of multiple row keys is used. When only one row key is held in the variable (INST_LIST =A-10"), the RList SELECT statement resolves correctly. But that is equivalent to WITH … AND WITH … AND WITH …

Mark

Mark,

Curious indeed. I tried a similar Rlist LIST command without any problem. But if there is a problem I'd like to know. Some of the functionality in my apps depends heavily on Rlist SELECTs with many clauses. Even if I haven't used the compact WITH =X" "Y" syntax much so far. I seem to prefer the explicit WITH =X" AND WITH =Y". And mostly with parentheses too. Perhaps there's a reason lost in the mists of time - some problem I might have had once with the compact syntax. The only thing I now seem to half remember (ugh) was that = was the only operator I got to work with the compact notation. I too put my SELECTs together programmatically - not just the search value but also the operator, and the operator might often be either of =, ], and .

Tell me: Is the use of variables significant? It shouldn't be of course. What I mean is: Are you really sure the command you execute is exactly what you think? Put the whole thing into a variable and inspect it with the Debugger.

Also tell me: Which false hits do you get? Your command is something like LIST/SELECT … ( WITH F=X" "Y" "Z" ) AND …. Which of "X", "Y" and "Z" are not filtered away? E.g try with extreme date values that should filter all of them away. Which of them slip through?

Bizarre idea: You say all, or almost all hits slip through. It could almost sound like you get your query executed as if without any parentheses, i.e, … WITH F=X" "Y" "Z" AND …, which I think can be equivalent to … ( WITH F=X" ) OR ( WITH F=Y" ) OR ( ( WITH F=Z" ) AND … ). "X" and "Y" will then not be filtered away.

- Oystein -

View this thread on the forum...