Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: SQL Select statement 2 very similar versions. Thought they were both valid ANSI SQL but various SQL engines parse these out differently.

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

From: Fredrik Ålund <fredrikREPLACEWITHADOTalund_at_upright.se>
Date: Fri, 19 Dec 2003 14:53:35 +0100
Message-ID: <bruvs2$ppu$1@green.tninet.se>


Both a and b corresponds to Full SQL 92. To check a statement for SQL compliance you can use our free tool SQL Validator at http://developer.mimer.com/validator. You can check if a statment corresponds to SQL 92, SQL 99 and SQL 200x draft.

Regards,
Fredrik Ålund

Mike & Janet Larke wrote:

> 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:53:35 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US