Archives of the TeradataForum

Message Posted: Fri, 19 Sep 2002 @ 19:47:32 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Did you try it?

To save you the effort, copy and paste this into queryman. I think you will find it doesn't delete anything you don't ask it to.

BTW. I am surprised the original macro picked up the NULLs. Possibly it has been tightened up in V2R4. I was brought up on the belief that null is an unknown value. As such, NULL can not possibly equal NULL. For example if two peoples ages are unknown (i.e. NULL), you can't assume that they are the same age. To test if both of the ages are unknown you need to write the conditional expression (as opposed to a state the statement :-) as (person1.age is unknown and person2.age is unknown).

The delete as I have supplied can be read as follows:

Delete the record where both the following are true:

1. the value of c1 is as specified and

2. either of the following are true

a) the value of c2 is as specified (i.e. an actual age has been provided eg. 39) or

b) (both the value of c2 is unknown and the value we wish to delete (i.e. :c2) is unknown

-------------------
The example that proves the technique works.

create table t  (c1 integer, c2 integer, c3 integer);
insert into t values (1, null, 1);
insert into t values (1, 1, 2);
insert into t values (1,2,3)
insert into t values (2, 1, 4);
insert into t values (2, null, 5);

replace macro dt (c1 integer, c2 integer)
as
(
delete from t
where c1 = :c1 and
    ((c2 = :c2) or (c2 is null and :c2 is null))
    ;
);

select *
from t
order by c3;

exec dt (1, null);
-- the record where c3 = 1 is gone.
select *
from t
order by c3;

select *
from t
order by c3;

exec dt (1, 1);
-- the record where c3 = 2 is gone.
select *
from t
order by c3;

Glenn Mc

	<Prev	Next>		<<First	<Prev	Next>	Last>>