|
|
Posted by fitzjarrell@cox.net on 10/01/34 11:39
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
Navigation:
[Reply to this message]
|