Okay this select statement:

SELECT SALES WITH THIS_GC=1 AND WITH STATUS_CODE=20 30 AND WITH SCAN_PROJ LE "02-15-05" AND WITH SCAN_PROJ NE ""

ARev 3.12:

Returns 118 hits

OI (using RLIST(statement etc).:

Returns 232 hits

STATUS_CODE is BTREE indexed, THIS_GC is a Boolean symbolic.

Examining the data returned in OI it looks like it is returning all the records with THIS_GC=1 AND STATUS_CODE=20 plus all the records with STATUS_CODE=30 AND SCAN_PROJ LE '02-15-05' AND SCAN_PROJ NE In OI or ARev SELECT SALES WITH THIS_GC=1 and STATUS_CODE=20 returns 42 hits SELECT SALES WITH STATUS_CODE=30 AND WITH SCAN_PROJ LE '02-15-05' AND SCAN PROJ NE

returns 190 hits

42 + 190=232 total hits

In OI if I run SELECT SALES WITH STATUS_CODE=20 30 followed by SELECT SALES WITH THIS_GC=1 AND WITH SCAN_PROJ LE '02-15-05' AND WITH SCAN_PROJ NE I get the expected 118 hits on the second select. It looks like the implied OR on STATUS_CODE is the culprit, but I don't know if it would behave the same on a non-indexed field. Any thoughts? This is a potentially serious behavioral difference as we have dozens of reports that use the implied OR in the selects. </QUOTE> —- === At 22 FEB 2005 05:54PM Jim Peters wrote: === <QUOTE>Warren, I ran into a similar situation involving 7.0 a few months ago. There was some 'spirited discussion' as I about swallowed my tongue upon discovering this shortly after having gone live with an application. In that case it seemed that if a 2nd or subsequent comparison was made of a field against a symbolic field (instead of a literal) I got nonsense results like you describe. Talk about making your heart skip a beat! I ended up going through the application with a fine toothed comb, and realized I (personally) never use that particular syntax within program code… So the app was fine. Phew! I found the problem when I was trying to make the early version report builder do something it was not yet smart enough to handle. So, in this case work around and avoid was the strategy that worked. They may have fixed this in 7.1 (Bill?), but if not you can probably find ways to avoid the errors once you know about them. You will run into something like this once in awhile, but overall moving an Arev application to this environment goes smoother than I would have expected considering the differences in technology. I would suggest trying it on 7.1 before going to too much trouble. Jim </QUOTE> —- === At 23 FEB 2005 01:21PM Warren Auyong wrote: === <QUOTE>Actually this is in v7.01 but it probably is the same in 7.0 I'll have to review the earlier thread, I didn't pay much attention to it. Do you recall what conditions caused the behavior? Is it only on Indexed fields? If so that could be nasty because what happens if you add a btree index…do you have to rewrite all your selects? </QUOTE> —- === At 23 FEB 2005 03:44PM John Bouley wrote: === <QUOTE>Warren, I believe this is one of the differences between OI and AREV selects. I also don't think it has anything to do with indexes. What kind of results do you get if you place parens around the implied OR? Not sure about 7.1. Please let us know what you find. </QUOTE> —- === At 24 FEB 2005 03:25AM The Sprezzatura Group wrote: === <QUOTE>There is a definite difference in how OI and ARev handle implict AND and OR functionality, and how it binds sentences together. OI shares an AREV 3.00 through 3.111 syntax. Because you'll be used to AREV's syntax, the safest bet is to surround all your selects with parenthesis to gain the implied criteria you desire. The Sprezzatura Group World Leaders in all things RevSoft </QUOTE> —- === At 24 FEB 2005 11:10AM Warren Auyong wrote: === <QUOTE>I tried parenthesis, the RLIST subroutine and SLIST did not seem to accept these giving me errors like "30)" does not exist in the dictionary. I tried surrounding the select value in quotes or adding a space to no advantage. </QUOTE> —- === At 24 FEB 2005 12:22PM Warren Auyong wrote: === <QUOTE>Parens was the first thing I tried. The RList subroutine does not seem to understand parens around selection clauses. </QUOTE> —- === At 24 FEB 2005 02:14PM John Bouley wrote: === <QUOTE>Warren, I am using 7.01 and have found the following works for me: sent=SELECT CUSTOMERS ( WITH TAX_STATE=NH" ) AND ( WITH CLASS1 "008" "002" )' call rlist(sent,5,,,)

Both Tax_State and Class1 are Btreed. I also tried it without the Parens and it returned AREV 3.12 like results…

HTH,

John

Warren,

Can you post or mail the selects your erroring with? S/List only passes select criteria into RLIST, but there's still the possibility we are parsing some things incorrectly for the pass.

The Sprezzatura Group

World Leaders in all things RevSoft

SELECT SALES WITH THIS_GC=1 AND WITH STATUS_CODE=20 30 AND WITH SCAN_PROJ LE "02-15-05" AND WITH SCAN_PROJ NE ""

When I put parens around any of the clauses SList gives errors as above. If I put it into RLIST subroutine directly either in a Stored procedure or from the system monitor I get no results. Maybe a beep but no message in monitor window

We know this is counter intuitive but implicit ORs have given us problems before now. Can you try

Quoting the numeric values

Using the fully expanded syntax

and see if there is a difference?

This is a debug attempt not a solution :)

support@sprezzatura.com

The Sprezzatura Group Web Site

World Leaders in all things RevSoft

It appears the syntax parser is picky/buggy (tested in OI 7.1):

parens can only be on the first WITH clause, anywhere else results in an error.

Works:

sent=SELECT TEST (WITH PARM2 '1015') AND WITH GC EQ '31'" Fails (from TCL returns ""31"" is not a valid column in "TEST", RLIST sub just 'dings' in SP): sent=SELECT TEST (WITH PARM2 '1015') AND (WITH GC EQ '31')"

sent=SELECT TEST WITH PARM2 '1015' AND WITH GC EQ '31'" </QUOTE> —- === At 26 JUL 2005 07:51AM Warren Auyong wrote: === <QUOTE>This is with RUN_REPORT or TCL (which probably is a shell around RUN_REPORT). I'll test on RLIST as a select statement and see what happens. </QUOTE> —- === At 28 SEP 2005 04:13PM Warren Auyong wrote: === <QUOTE>Okay it seems parens around WITH clauses are unacceptable for the RLIST subroutine in OIv7.1.1 e.g. SENT=SELECT SALES (WITH STATUS_CODE "10""15") AND WITH D_I_ GT "09-01-05"' RLIST(SENT,5,,) Blows up, the system monitor (sometimes) shows an error "(WITH is an invalid dictionary item". SENT=LIST SALES (WITH STATUS_CODE "10""15") AND WITH D_I_ GT "09-01-05"' RUN_REPORT(,SENT)

Works, but the same limitation apply, that is parens on first WITH clause only.

However it appears that v7.1.1 is handling the IMPLIED ORs in the same manner as ARev 3.12, or at least with the tests I've run so far I get the same hits back from ARev v3.12 and OI v7.1.1 with or without the parens.

So I'm confused now if I need to rewrite my selects once again since upgrading to v7.1.1

You need a space both sides of (

View this thread on the Works forum...