Here are two versions of a SQL SELECT. The only difference is a set of
parens around the expression "SUM(COL1)*100 / SUM(COL2)" which are present
in a) and absent in b).

These parens appear to impact the parsing significantly under different SQL
engines even though from my perspective they are identical in logical
evaluation. The extra paren set in a) definitely makes the entire SQL read
more easily but should not affect the validity/result . . . you would think.
(?) But it does!

SQL engines (eg NexusDb and SQLMemTable) will not accept a) , but will
accept b). Using a) they complain of a lack of a GROUP BY for a
non-aggregate column. Curiously TxQuery SQL engine will accept a) but not
b).

Can anyone tell me which of a) & b) are the ANSI standard construct?
Or suggest some ideas to try to force correct parsing from all(most) SQL
engines?

Thank you very much.

a)
SELECT * FROM
(
SELECT (SUM(COL1)*100 / SUM(COL2)) AS RESULTCOL
FROM ATable
WHERE (COL1>0)
AND (COLX IN ('A','B'))
AND (COLY IN ('A','B'))
AND (COLZ IN ('A','B'))
)
WHERE (RESULTCOL > 5.00000);

----------------

b)
SELECT * FROM
(
SELECT SUM(COL1)*100 / SUM(COL2) AS RESULTCOL
FROM ATable
WHERE (COL1>0)
AND (COLX IN ('A','B'))
AND (COLY IN ('A','B'))
AND (COLZ IN ('A','B'))
)
WHERE (RESULTCOL > 5.00000);

"Mike & Janet Larke" <larke (AT) cableone (DOT) net> wrote in
news:vu5tcilf3qi603 (AT) corp (DOT) supernews.com:

Both a) and b) are ANSI compliant... (Core SQL-99 plus the "Derived
tables" extension.)

The inner SELECT will return one single value, SUM(COL1)*100 / SUM(COL2).

The outer SELECT will then return that value if its > 5.0000, if not no
row at all will be returned.


/Jarl

Mike & Janet Larke wrote:
I've never heard of those 3 "SQL Engines", but they are obviously poor implementations.
Parentheses should make no difference in that context.

Yes, they are standard constructs (since the beginning). To force commonality between
those 3, you may need to nest deeper ... first calculate the SUM() and do the
calculation in a more outer SELECT.

--
Lee Fesperman, FirstSQL, Inc. (http://www.firstsql.com)
================================================== ============
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)