That was great!!!
asoutar, October 05, 2001 - 4:24 pm UTC
I could not find detail like that any where on the internet. Thank you very much!!
A few questions about SQL 92
Dusan, April 08, 2002 - 11:52 am UTC
1) What are "Domains", at Intermediate level?
2) Which SQL 92 features are still not included into 9i?
3) Is "Cascade Update" part of SQL 92?
Thanks,
Dusan
April 08, 2002 - 4:00 pm UTC
Oracle is certified to SQL92 entry level (as high as anyone else)
We all have features from higher levels (all being the other certified databases).
SQL92 is a HUGE spec -- there is no way I could list the features of intermediate, transitional and full that we do not have. No way. They only thing you can count on is what is specified for entry level -- thats the "level playing field"
Domains are like sets of values -- they are the "domain of values".
Don't know if cascade update is or not -- I have yet to see a properly designed database where it was necessary. I'm a believer that primary keys are imutable -- if there is even the SLIGHTEST chance you need to update them -- they are NOT the key, something else is.
Helena Markova, April 09, 2002 - 1:42 am UTC
Thank you for explanation.
Thanks a lot for explanation about Standard levels !!!
MaxU, April 09, 2002 - 10:36 am UTC
"SQL Standards"
Kurt Schmidt, December 01, 2005 - 4:45 pm UTC
Ask Tom is still a standard in itself -- and none better!
Better than ANSI Standard for years
Duke Ganote, December 05, 2006 - 8:57 am UTC
ANSI and ISO
Duke Ganote, July 10, 2007 - 5:13 pm UTC
Is this possible
Chinni, February 16, 2009 - 4:25 am UTC
Hi Tom,
Is it possible to write oracle sql which is compatible to other databases?
example
ORACLE:
select * from emp where trunc(hiredate)=trunc(to_date(sysdate,'dd-mon-yyyy') )
Here trunc, to_date are specific to oracle ( as of i know)
Can this query be rewritten using some standard so that it will be compatible for other databases for example H2 or HSQLDB
I don't know if something like to is possible
February 16, 2009 - 12:38 pm UTC
hah, good old database independence, good luck with that.
select x, count(*) from t group by 1;
that works in teradata, not so in Oracle (and there are hundreds that go the other way, and hundreds that go the other other way and so on)
And what about concurrency controls? What about the fact that reads don't block writes (in Oracle) but do in most others? What about the fact that application logic that works in SQLServer fails miserably in Oracle (and vice versa).
You'd have to gather together the set of databases you want to be independent over.
You'd have to document their features/functions (and how they work - just because we both have the keyword FOO does not mean FOO does the same thing)
Then you'd have to find the intersecting set of functionality, otherwise known as the lowest common denominator
And then you could set about writing some of the slowest possible database applications ever seen that have data integrity issues out the wah-zoo
GROUP BY - is it optional?
Parthiban Nagarajan, February 05, 2011 - 9:13 am UTC
Hi Tom
(o) Is it true that the ANSI SQL Standards has a change to make the GROUP BY optional?
(o) Any plan for Oracle to adopt the suggested change?
(o) What is your view on that? I mean, does leaving the GROUP BY in a query makes it more meaningful? Could you explain with an example?
Best Regards
February 06, 2011 - 12:13 pm UTC
(a) I don't know
(b) see (a) - if it isn't part of the standard - why would we?
(c) first, queries do not need a group by.
select count(*) from t;
second - if they need it - they need it
select count(*) from t group by a;
If you leave it off - you get the wrong answer.
In some cases, the group by could be considered pedantic:
select a, count(*) from t group by a;
It could be said that "you could figure out you need to group by a" - but I don't see the problem with being explicit. Else you get lazy and make mistakes - perhaps you MEANT to group by a,b - but left it off and we just grouped by a. Sort of like an implicit conversion - something to be avoided.
sql-92 compliant
A reader, April 04, 2012 - 5:27 am UTC
Hi Tom,
Can you help me to understand this:
DB2 and Teradata are SQL-92 compliant and Oracle is not, as empty string (DB2) while loading to Oracle database becomes null?
Regards,
April 04, 2012 - 12:13 pm UTC
no one is sql-92 compliant - anyone that tells you they are - is making it up.
teradata for example lets you "select a, b, count(*) from t group by 1, 2" in clear violation of ansi sql-92. That statement should fail.
NIST hasn't done an ANSI evaluation since 1993 - and that last one was to certify....
Oracle as sql92 compliant.
But they only test a small bit of a huge specification. And no one tests now, there is no way to certify, no one issues certifications.
Loading empty string
A reader, April 04, 2012 - 3:29 pm UTC
Hi Tom,
Thanks for quick response.
We have env., source - DB2, intermediary - Oracle (11g), target Teradata. When we read from DB2 it is empty string, and without writing code, oracle converts to null, but we need empty string in Teradata. What is the best way to do this?
Regards,
April 04, 2012 - 8:18 pm UTC
realize that all databases are inherently different? Program accordingly?
there are thousands of differences between A and B
and B and C
and C and A
and so on.
I'm very glad to answer questions about Oracle, I can do that.
I cannot answer questions about the best way to do something in DB2 or Teradata.