Monday, July 18, 2005

SELECT MAX(NULL) FROM DUAL;

Never do that.
Actually, never SELECT MAX(col) FROM tab where col is a NULLable column on an Oracle client. Oh, and don't forget that an empty string is a NULL on Oracle.
It seems that the .NET framework has a bug, and, when you do this, you'll get a NullReferenceException from the .NET Oracle provider.
Weird.
Time to review all my SELECT MAX, SELECT MIN and convert from:
SELECT MAX(col) FROM tab
to
SELECT MAX(col) FROM tab WHERE col IS NOT NULL

Comments:
Probably the better solution will be :

SELECT MAX(
CASE
WHEN XX IS NULL
THEN 0
ELSE XX
END)
FROM XX
 
actually the best would be the following:

select (max(COALESCE(XX,0)))

when 0 is the minimum value your column can assume
 
mostly i use first one.that much usable to filter data
 
Very impressive stuff. Thanks for sharing
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?