Skip to Main Content

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Andrew.

Asked: October 05, 2001 - 12:58 pm UTC

Answered by: Tom Kyte - Last updated: April 04, 2012 - 8:18 pm UTC

Category: SQL*Plus - Version: 8.1.6

Viewed 1000+ times

You Asked

Hi Tom,
I can not find this information anywhere.

insert into ISSYEAR
2 select SUM(if(IF BSIS.BSIS = B, PROJMARCH+PROJJAN+PROJFEB)) AS Q1,
3 PROJYEAR
4 FROM BSIS, FLATFILETEST
5 where BSIS.VARIALBE = FLATFILETEST.VARIABLE;

Is the sum(if supported in 8.1.6. ANSI/ISO SQL:1992

The error from this statement is:
ORA-00907: missing right parenthesis

8.1.6 is ANSI/ISO SQL:1992 standard.
9.i is ANSI/ISO SQL 1999 standard.

Do you know where I could get information on these standards?



and we said...

Starting with the standard, we will find that the SQL standard has four
levels:

o Entry-level: This is the level to which most vendors have complied. This
level is a minor enhancement of the predecessor standard, SQL/89. No database
vendors have been certified higher and in fact the National Institute of
Standards and Technology (NIST), the agency that used to certify for
SQL-compliance, does not even certify anymore. I was part of the team that got
Oracle7.0 NIST-certified for SQL/92 entry-level compliance in 1993. An entry
level compliant database has the feature set of Oracle7.0.

o Transitional: This is approximately 'halfway' between entry-level and
intermediate-level as far as a feature set goes.

o Intermediate: this adds many features including (not by any means an
exhaustive list):
Dynamic SQL
Cascade DELETE for referential integrity
DATE and TIME data types
Domains
Variable length character strings
A CASE expression
CAST functions between data types

o Full: Adds provisions for (again, not exhaustive):
Connection management
A BIT string data type
Deferrable integrity constraints
Derived tables in the FROM clause
Sub-queries in CHECK clauses
Temporary tables

The entry-level standard does not include features such as outer joins, the new
inner join syntax, and so on. Transitional does specify outer join syntax and
inner join syntax. Intermediate adds more, and Full is, of course all of SQL/92.

Most books on SQL do not differentiate between the various levels leading to
confusion on the subject. They demonstrate what a theoretical database
implementing SQL FULL would look like. It makes it impossible to pick up a
SQL book, and apply what you see in the book to just any SQL database. For
example, in SQL Server the 'inner join' syntax is supported in SQL statements,
whereas in Oracle8i it is not (but 9i does). Then again, Oracle7 supports
derived tables in the FROM clause (inline views) which is part of FULL but
others do not.

But, they are both SQL-compliant databases. You can do inner joins and outer
joins in Oracle, you will just do it differently than in SQL Server. The bottom
line is that SQL will not go very far at the entry-level.

All databases implement many of the features of the higher levels but do not
fully comply with the higher levels.


Now, as to how to do your statement above, either:

select sum( case when bsis = b then PROJMARCH+PROJJAN+PROJFEB
else 0
end ) as q1,
projyear
from ....


or more simply:

select sum( decode( bsis, b, PROJMARCH+PROJJAN+PROJFEB, 0 ) ) as q1,
projyear
...


if the issue is "well that makes my application Oracle dependent" -- use the CASE (thats ANSI) or use a view (best approach) and just port the views.








and you rated our response

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

That was great!!!

October 05, 2001 - 4:24 pm UTC

Reviewer: asoutar from Toronto,on

I could not find detail like that any where on the internet. Thank you very much!!

A few questions about SQL 92

April 08, 2002 - 11:52 am UTC

Reviewer: Dusan from Czech Rep.

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

Tom Kyte

Followup  

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.

April 09, 2002 - 1:42 am UTC

Reviewer: Helena Markova from Bratislava, Slovakia

Thank you for explanation.

Thanks a lot for explanation about Standard levels !!!

April 09, 2002 - 10:36 am UTC

Reviewer: MaxU from Munich, Germany


"SQL Standards"

December 01, 2005 - 4:45 pm UTC

Reviewer: Kurt Schmidt from Seattle, WA, USA

Ask Tom is still a standard in itself -- and none better!

Better than ANSI Standard for years

December 05, 2006 - 8:57 am UTC

Reviewer: Duke Ganote from http://maps.google.com/maps?q=39.056690,-84.245720+(Near+here)

Oracle's Analytic Functions (available in 1999's version 8.1.6) were innovative, and apparently accepted as ANSI standard SQL2003 (but termed "windowing functions").

Now available in SQL Server 2005:
</code> http://www.sqlmag.com/Article/ArticleID/48297/sql_server_48297.html
and certain flavors of DB2:
http://safari.oreilly.com/0596004818/sqlnut2-CHP-4-SECT-3 <code>
(not available in the flavor -- DB2 for z/OS -- I get to deal with, of course, yet).

ANSI and ISO

July 10, 2007 - 5:13 pm UTC

Reviewer: Duke Ganote from http://maps.google.com/maps?q=39.056690,-84.245720+(Near+here)

Is this possible

February 16, 2009 - 4:25 am UTC

Reviewer: Chinni

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
Tom Kyte

Followup  

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?

February 05, 2011 - 9:13 am UTC

Reviewer: Parthiban Nagarajan from Coimbatore, India

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
Tom Kyte

Followup  

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

April 04, 2012 - 5:27 am UTC

Reviewer: A reader

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,

Tom Kyte

Followup  

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

April 04, 2012 - 3:29 pm UTC

Reviewer: A reader

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,

Tom Kyte

Followup  

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.