https:

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

 < Last>>

 Subj: Re: Case Statement in Macro From: McCall, Glenn D

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

 < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2002 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback