SQL Select statement 2 very similar versions. Thought they were both valid ANSI SQL but various SQL engines parse these out differently.

From: Mike & Janet Larke <larke_at_cableone.net>
Date: Fri, 19 Dec 2003 07:05:09 -0600
Message-ID: <vu5tsc23gd7q2d@corp.supernews.com>

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); Received on Fri Dec 19 2003 - 07:05:09 CST