why the SQL Standard hardly matters...

[r937]

without looking it up (assuming you actually have a copy of the SQL Standard), is the following valid SQL?

select 1, * from mytable

hint: no, it isn't



r937.com | rudy.ca

 

[Zathras]

It may not be valid ANSI SQL, but it works in Microsoft SQL Server.

and for what it's worth, this variation works in Gupta SQLBase:
[tt]
select 1,mytable.* from mytable
[/tt]
What's your point?

 

[r937]

that's right, it isn't valid sql, but it works in every database i've tried it in, too

the point is, do not place too much faith in what is and what isn't standard sql

what matters is, will it work in your database system

r937.com | rudy.ca

 

[JarlH]

And Mimer, MySQL, DB2, and Oracle do not accept it. (While SQL Server, Postgres and Sybase do.)

So my conclusion is that if you want to write portable code, the SQL standard is a little bit safer way to go.

If you know your application will never ever be ported to anbother DBMS, you don't have to care about whats standard or not. (But why wasting time in this forum then...)

 

[Zathras]

It would be nice if vendors of DBMS would adhere to the SQL standard and be content with adding extensions, but sadly that does not seem to be the case.

I'm thinking specifically of || for concatenation. I believe that is the standard, but Micro$oft SQL Server does not allow it. One must use + for concatenation.

So when someone posts here with a question about concatenation, what would be the preferred course?
1. Give the "standard" answer and send him on his way? (He'll probably post again when he tries it and it doesn't work.)
2. Ask what DBMS is he using first?
3. Give all of the possibilities?

Then if he indicates that he is using SQL Server, do we
4. Give him a stern admonishment that this is the ANSI SQL Forum and SQL Server does not do it the standard way?
5. Answer the question and then give the admonishment?
6. Don't answer, but tell him to post again in one of the SQL Server fora?
7. Wait for someone else to answer the question with instruction to use "+" and then admonish the responder with "That is not ANSI SQL."?

 

[Sashanan2]

7 sure sounds fun

But it's inevitable that the differences between SQL implementations (and having different forums for each) is going to cause confusion to those who aren't yet aware that these differences are in place, so you can't really prevent questions from showing up in the wrong places sometimes. The best approach would seem to be to give the right answer and educate on the topic at the same time (thus making sure one more person knows to be wary of SQL differences), though that's also the most time consuming one.


"Any fool can defend his or her mistakes; and most fools do." -- Dale Carnegie

 

[r937]

zathras, thanks, nice post

yes, it was the concatenation thread the other day that got me thinking...

maybe we need a FAQ on this, but don't ask me, i hate writing stuff that no one will read anyway

as for your choices, i prefer option 1, it's the simplest



r937.com | rudy.ca

 

[rac2]

5b. Answer the question and then gently point the way.

But still the question remains, why does the standard matter?

Maybe it is like a data conversion hub where old data in format A is converted to an arbitrary central format, then converted to the new format B. So we need 2n conversion operations instead of n x (n-1) operations to handle n formats.

Or maybe it is like knowing a large system and a short list of exceptions.

What if SELECT were not a standard notation. Imagine
Oracle with CHOOSE
PostGre with CHOISI
MySQL with SHOW
MS SQL with PROMOTE
Access with QUERY

 

[r937]

a short list of exceptions? shirley, you jest

consider the sql to add one month to a date value...



if you like 5b, which came before it, 1, 2, or 3?

r937.com | rudy.ca

 

[juliane26]

Well, this is an ANSI forum and quite a number of users do know it. So I'd answer ANSI as well. (number 1)
Usually you don't know yet about the used DBMS.
Once you know - 5b is fine and right.

But anyway, The most users not knowing the difference seem to be MS SQL Server (or even Access) and I wonder why.
I mean SQL Server really did a lot to catch up with the standard and they are promoting that.
May be because of the name, having 'SQL' Server and ANSI 'SQL'??
And then - it is slightly annoying reading questions about how to get MSDN 2003 working with Servicepack 3 ... to be honest I just ignore.




Juliane

 

[r937]

one thing we should all perhaps pay more attention to is red-flagging any vague subject titles

yes, that's in all the faqs, but when someone posts a thread with a title that reveals nothing about the contents, and you open it up and find it is something you didn't want to see (great example, Juliane, how to get MSDN 2003 working with Servicepack 3), then you should red flag the post and ask tek-tips administrators to rename the thread

provide a suggested new title to make the process really quick, and you will be helping other forum users save time too


r937.com | rudy.ca