Re: one for the SQL experts - dare I say TRICKY SQL!

> Standard to which DBMS? Certainly not Oracle. I will submit passing
> date strings without a proper format specifier is poor coding:

The ISO standard rather than vendor specific.

> 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))

You do not and would not code it like that in SQL Server, you would simply write...

INSERT INTO Threads ( thread_id, start_stamp, stop_stamp )

    VALUES(1, '2006-02-07T02:03:00', '2006-02-07T02:07:00') The rest of the post, basically I was refering to the ANSI 92 INNER JOIN, CROSS JOIN syntax over the ANSI 89 comma syntax.

We got the ANSI 92 syntax in version 6.5 of MS SQL Server which was around 96/97, the majority 99.9% of people in the MS SQL Server space using ANSI 92 now and convert what I term the 'out-dated' syntax to ANSI 92.

I didn't see the cross posting news groups so the syntax specific stuff refers to MS SQL Server, not sure Oracle and Sybase got it until the last few years so you'll go through a similar curver imho.

Tony.

-- 
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


<fitzjarrell_at_cox.net> wrote in message 
news:1139538033.974291.324400_at_g14g2000cwa.googlegroups.com...


> 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

>