| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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!
Comments embedded.
Tony Rogerson wrote:
> > INSERT INTO Threads VALUES(1, '2006-02-07 02:03:00', '2006-02-07
> > 02:07:00');
>
> This is very dangerous code, its worse than SELECT * and relies columns
> being in order which we know in a set is just not the case.
>
> ALWAYS specify the columns on your INSERT...
>
> INSERT INTO Threads ( thread_id, start_stamp, stop_stamp ) VALUES(1,
> '2006-02-07 02:03:00', '2006-02-07 02:07:00')
>
To this I heartily agree.
> Also, use standard formatting for the dates - '2006-02-07T02:07:00'
>
Standard to which DBMS? Certainly not Oracle. I will submit passing date strings without a proper format specifier is poor coding:
INSERT INTO Threads ( thread_id, start_stamp, stop_stamp ) VALUES(1, to_date( '2006-02-07 02:03:00', 'YYYY-MM-DD HH24:MI:SS'),
to_date( '2006-02-07 02:07:00', 'YYYY-MM-DD
HH24:MI:SS'))
For those using SQL Server:
INSERT INTO Threads ( thread_id, start_stamp, stop_stamp ) VALUES(1, convert(datetime, '2006-02-07 02:03:00', 120),
convert(datetime, '2006-02-07 02:07:00', 120))
One should never assume a universal date/time format.
> > SELECT M.meas_stamp, COUNT(T.thread_id) AS active_tally,
> > SUM(M.meas_value) AS meas_tot
> > FROM Threads AS T, Measurements AS M
> > WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp
> > GROUP BY M.meas_stamp;
>
> Stop using that outdated column syntax nobody except oldbies unwilling to
> change use.
>
Nothing wrong with using it as it returns the proper results. I will admit once one is accustomed to using the ANSI join syntax it is easier to write and prettier to view. But, ugliness doesn't make it wrong.
> SELECT M.meas_stamp, COUNT(T.thread_id) AS active_tally, SUM(M.meas_value)
> AS meas_tot
> FROM Threads AS T
> CROSS JOIN Measurements AS M
> WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp
> GROUP BY M.meas_stamp;
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
>
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.
David Fitzjarrell Received on Thu Feb 09 2006 - 20:20:34 CST
 |
 |