Always RTFM
Peter Nosko, February 15, 2012 - 12:46 pm UTC
Tom, many thanks (I love solutions involving less typing and more clarity). I made an assumption about the Decode comparison following the "rule", but now notice the "disclaimer" at the end of the function's Purpose.
In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null.
And for PL/SQL?
Michael, February 16, 2012 - 6:49 am UTC
DECODE works great for this in SQL.
Unfortunately, it does
not work in PL/SQL:
begin
if decode(1, 2, 3) = 4 then null; end if;
end;
/
ORA-06550: line 2, column 6:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL statement only
Tom, are there any plans to make DECODE available in PL/SQL (of course without the support for scalar subqueries as parameters)? (Please say "yes".)
February 16, 2012 - 7:30 am UTC
You would either have to select it from dual, or more optimally - just have a small utility package:
ops$tkyte%ORA11GR2> create or replace package utl
2 as
3 function isEqual( a in number, b in number ) return boolean;
4 function isEqual( a in varchar2, b in varchar2 ) return boolean;
5 function isEqual( a in date, b in date ) return boolean;
6 /* ... any other types you desire */
7 end;
8 /
Package created.
ops$tkyte%ORA11GR2> create or replace package body utl
2 as
3 function isEqual( a in number, b in number ) return boolean
4 is
5 begin
6 return (a=b) or (a is null and b is null);
7 end;
8
9 function isEqual( a in varchar2, b in varchar2 ) return boolean
10 is
11 begin
12 return (a=b) or (a is null and b is null);
13 end;
14
15 function isEqual( a in date, b in date ) return boolean
16 is
17 begin
18 return (a=b) or (a is null and b is null);
19 end;
20
21 /* ... any other types you desire */
22 end;
23 /
Package body created.
no plans to implement a varying length parameter list type of function in PLSQL directly...
Bug? NULL is sometimes nasty
Michael, February 16, 2012 - 9:20 am UTC
Hi Tom,
i afraid: you didn't say "yes"!!! ;-)
So i have to stick with my utitlity package for that. :-(
But, i think there is a little bug in your utl package:
Shouldn't the functions always return either TRUE or FALSE (and never NULL)?
(I suppose they should only return TRUE or FALSE otherwise it would be nasty to use them in IFs.)
If
one of the inputs is NULL, the isEqual functions return NULL, because (X is some value):
(X=NULL) or (X is null and NULL is null)
=
NULL or (FALSE and TRUE)
=
NULL or FALSE
=
NULL
So, the code should look more like that:
function isEqual( a in number, b in number ) return boolean
is
begin
return (a is not null and b is not null and a=b) or
(a is null and b is null);
end;So that:
(X is not null and NULL is not NULL and X=NULL) or
(X is null and NULL is null)
=
(TRUE and FALSE and NULL) or (FALSE and TRUE)
=
FALSE or FALSE
=
FALSE
February 16, 2012 - 10:32 am UTC
yes, my function returns TRUE and NULL, your approach would be better.
re Followup
Robert Van Den Hoek, February 16, 2012 - 5:19 pm UTC
Hi Tom.
I just need to say, you are one of the best technical writers that I have ever "read" in my life. You explain things incredibly clearly, always giving awesome examples, you call a spade a spade, and if someone ever writes or posts an idea or code that is superior to yours (which is rare, but does happen), then you acknowledge it.
Thanks again for all your words of wisdom over the years, both here and in your books - you've helped me so much in my Oracle career.
Cheers,
Rob
David Aldridge, February 20, 2012 - 4:19 pm UTC
I like the method of checking NVL(col1,CHR(0)) = NVL(col2,CHR(0)) -- it's broadly analagous to the undocumented sys_op_map_nonnull function which maps values to a RAW in which nulls are represented as 255. 0 seems to me to be a better choice, though if in the future Oracle's handling of nulls changed then that might be different? Buh, haven't really thought about it...
It does give you a way of efficiently joining on nulls (relational moderately-purists insert shudder here) by creating a function-based index on NVL(col1,CHR(0)).
Actually for purity's sake I might go with Coalesce(col1,Chr(0)), since Nvl offers nothing that Coalesce doesn't improve on (except screen ink).
I suppose it's theoretically possible to store chr(0) in a varchar2. The paranoid might like to use a check constraint to prevent such an abomination.
February 21, 2012 - 7:13 pm UTC
I'm not a fan because it is relying on a "trick", and you are relying on an undocumented bit of behavior (the chr(0) bit being a value not used to mean something).
It is not only theoretically possible, it is possible :)
If you need to consider NULL's equal in a join, I think you've made a fundamental mistake in your data model somewhere
Peter Nosko, February 21, 2012 - 8:54 pm UTC
Tom replied-- If you need to consider NULL's equal in a join, I think you've made a fundamental mistake in your data model somewhere
In DW loading of type 2 dimensions, OWB generates the 3-condition check that I originally asked about for columns set to "Trigger History." It is valid to assume that a column value didn't change when a NULL in a transaction column is compared to a NULL in a dimension record column. I was just looking for a more efficient way to code it. (Efficient for the SQL engine, the developer or both.)