Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: one for the SQL experts - dare I say TRICKY SQL!
fitzjarrell_at_cox.net wrote:
>And, unfortunately for the SQL Server crowd this was also posted to
>comp.databases.oracle.server. Unfortunate because the modified example
>provided throws an error from SQL*Plus:
>
>ERROR at line 3:
>ORA-00933: SQL command not properly ended
>
>and is the result of using AS to declare the table aliases. Oracle
>simply doesn't accept it, and I'm fairly certain SQL Server can get by
>without it as well. To make the previously posted code 'palatable' to
>SQL*Plus:
>
>SQL> SELECT M.meas_stamp, COUNT(T.thread_id) AS active_tally,
>SUM(M.meas_value)
> 2 AS meas_tot
> 3 FROM Threads T
> 4 CROSS JOIN Measurements M
> 5 WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp
> 6 GROUP BY M.meas_stamp;
>
>
>MEAS_STAM ACTIVE_TALLY MEAS_TOT
>--------- ------------ ----------
>07-FEB-06 1 10
>07-FEB-06 2 20
>07-FEB-06 2 40
>07-FEB-06 2 40
>07-FEB-06 2 60
>07-FEB-06 1 40
>07-FEB-06 1 40
>
>7 rows selected.
>
>Note it's still using the 'prettier' ANSI syntax (and, again, simply
>because it's possibly ugly doesn't make the old style join syntax
>wrong), it simply removes the offensive (to SQL*Plus) AS verbiage when
>declaring the table aliases.
>
>
Just a quick (minor) note: 'AS' is defined in the ANSI SQL-99 standard
(ISO/IEC 9075-2:1999) as an optional keyword in the FROM clause between
the object name (table, view, derived table, whatever) and the alias for
it. So, while it's true that SQL Server can get by without it (it's
optional in the T-SQL grammar, as it is in SQL-99) and it's not defined
in the PL/SQL grammar, technically speaking, Tony's code was right in
that it conformed to SQL-99.
-- *mike hodgson* http://sqlnerd.blogspot.comReceived on Thu Feb 09 2006 - 20:46:09 CST