Archives of the TeradataForum
Message Posted: Fri, 19 Sep 2002 @ 19:47:32 GMT
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
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;
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|