Long overdue, today's Oracle gem of the day is actually an ANSI SQL-92 gem of the day. This answers a question I've had for a long time: how do you consistently handle NULLs across databases?
One thing I heard awhile back is that Oracle uses this strange "VARCHAR2" type instead of the normal VARCHAR because there is some aspect of VARCHAR that is undefined in the standard. Oracle's base type is VARCHAR2, and currently VARCHAR is just a synonym. If the standard ever gets clarified and it is different from the choice Oracle made to resolve the ambiguity, Oracle's VARCHAR will change while VARCHAR2 will stay the same. I'm under the impression that the main difference is sorting NULLs: some implementations put NULLs before non-NULLs when sorting, some do otherwise. (I may be completely wrong about all this.)
So I've often wondered what I could do in SQL to make NULLs sort the same in code that might be moved to different databases. The answer (well, the standards-based answer, and we all know what the great thing about standards is) is the SQL-92 CASE statement.
Oracle gives you a nice routine called NVL to provide default replacement values for NULLs. NVL is sort of like a defined-or operator for Perl. If everybody gave you NVL, you could just wrap all your possibly NULL columns in NVL and things would work identically across databases. Unfortunately NVL is not part of the standard, and every RDBMS gives you a different function to achieve this.
However, the ANSI standard CASE statement fits the bill. The following will select a column from a table turning all NULLs into the string "NONE":
SELECT CASE column
WHEN NULL THEN 'NONE'
ELSE column
END
FROM table;
Lovely, and it works everywhere! Actually, that's a complete lie. This was added into the SQL standard in 92. Oracle adopted it 8 years later, and I'll bet other databases have similar track records with compliance. If you have Oracle 8.1.6 or later, you can use this in SQL (must have Oracle 9i or later to use it in PL/SQL), and someday all databases will support it. If they feel like it.
(Note: I haven't completely tested this, yet, and I think there might be some issues with the difference between NULL, a null string, and a string full of spaces. YMWV.)
-Dom
porting DECODE (which was what most Oracle SQL programmers used before CASE was implemented) into MySQL.. scarred me for life, it did. Because PL/SQL programmers uses DECODE in lots of funky ways..
Re:the pain..
jdavidb on 2003-08-15T14:01:01
which was what most Oracle SQL programmers used before CASE was implemented
I'm sure it will be what most Oracle programmers use after CASE is implemented. It took eight years from standardization for Oracle to get it into the system
... figure twice that for general users to figure it out and start using it.