tom:
from above
... null keys are the source of great confusion
to
our users because they can't join on null keys.
...
if that is true, the users cannot do joins, they are not smart enough in the first place, they need to use a tool that already includes all of this and just gives them a "big old spread sheet" to query.
----
I've ran into issue with joins on NULL values.
I have a 'hack' but i don't know if it is a good/proper way to do it.
Is my 'hack' valid?
is there a better way to do it?
or did i just skip the page(s) in the books that go over joins with NULL values?
the set up:
I have a table that has zero or one row per "ID,sub_type" pair.
(initially 9iR2, but will be upgrading to 11gR2 this year)
create table calc_values (
item_id varchar2(10) not null,
sub_type varchar2(10),
result number
);
-- force maximum of one row
create unique index cv_uix on calc_values(item_id,sub_type);
create or replace procedure upsertCalcValues( p_item_id in varchar2, p_sub_type in varchar2, p_result in number )
as
begin
merge into calc_values CV
using ( select p_item_id as item_id,
p_sub_type as sub_type,
p_result as result
from dual ) DI
on ( CV.item_id=DI.item_id
and CV.sub_type=DI.sub_type )
when matched then update
set result=DI.result
when not matched then insert
(item_id, sub_type, result) values (DI.item_id,DI.sub_type,DI.result);
-- other stuff goes here
end;
/
-- add some data
execute upsertCalcValues('alice', null, 1);
execute upsertCalcValues('alice', 'strange', -3/2);
execute upsertCalcValues('alice', 'charm', 2/3);
execute upsertCalcValues('bob', null, 1);
execute upsertCalcValues('bob', 'up', 1/2);
execute upsertCalcValues('bob', 'down', -1/2);
-- update a row
execute upsertCalcValues('alice', 'charm', 3/2);
commit;
select * from calc_values;
ITEM_ID SUB_TYPE RESULT
---------- ---------- ----------
alice 1
alice strange -1.5
alice charm 1.5 <-- notice, only one 'alice','charm' row and RESULT is 3/2 not 2/3
bob 1
bob up .5
bob down -.5
6 rows selected.
-- try to update a row with a NULL value
execute upsertCalcValues('alice', null, 1.1);
ORA-00001: unique constraint (CV_UIX) violated
================
My hack to MERGE statement
-- There is no such thing as SUB_TYPE named 'null'.
-- SUB_TYPE IS NULL is valid. (and, actually, end-users prefer IS NULL over 'null')
alter table calc_values
add constraint cv_nonull_chk check ( sub_type != 'null' );
-- modify MERGE statement
...
on ( CV.item_id=DI.item_id
and
nvl(CV.sub_type,'null')=
nvl(DI.sub_type,'null') )
...
In 11g, i'd create a virtual column for the NVL() value and a PK with that column.
January 06, 2010 - 1:27 pm UTC
ops$tkyte%ORA10GR2> create or replace procedure upsertCalcValues( p_item_id in varchar2, p_sub_type in varchar2, p_result in number )
2 as
3 begin
4 merge into calc_values CV
5 using ( select p_item_id as item_id,
6 p_sub_type as sub_type,
7 p_result as result
8 from dual ) DI
9 on ( CV.item_id=DI.item_id<b>
10 and decode( CV.sub_type, DI.sub_type, 1, 0 ) = 1 )</b>
11 when matched then update
12 set result=DI.result
13 when not matched then insert
14 (item_id, sub_type, result) values (DI.item_id,DI.sub_type,DI.result);
15
16 -- other stuff goes here
17 end;
18 /
Procedure created.
<b>or</b>
ops$tkyte%ORA10GR2> create or replace procedure upsertCalcValues( p_item_id in varchar2, p_sub_type in varchar2, p_result in number )
2 as
3 begin
4 merge into calc_values CV
5 using ( select p_item_id as item_id,
6 p_sub_type as sub_type,
7 p_result as result
8 from dual ) DI
9 on ( CV.item_id=DI.item_id<b>
10 and ( CV.sub_type= DI.sub_type or (cv.sub_type is null and di.sub_type is null) )</b>
11 )
12 when matched then update
13 set result=DI.result
14 when not matched then insert
15 (item_id, sub_type, result) values (DI.item_id,DI.sub_type,DI.result);
16
17 -- other stuff goes here
18 end;
19 /
Procedure created.
In that fashion, you don't have to pick some value - who is to say that subtype 'null' isn't a valid value?