Re: Idiomatic SQL empty set?

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 08/04/2017
03:40:07 PM:

Sometimes I'll want all the rows where myfield2 is in (1, 2, 3). Other
times, I'll want all the rows where myfield2 is in (2, 4). Other
times, I'll want all the rows where myfield2 is in (5). And other
times, I'll want all the rows where myfield2 is in the empty set. The
actual set of desired values is completely arbitrary and determined at
run-time.

I would like a way to express "the set of desired values". Whatever
this expression is (let's call it desiredset) should be able to be
inserted into

select myfield1 from mylib.myfile where myfield2 in (desiredset)

When the set of desired values isn't empty, desiredset can consist of
a comma-separated list of literal values. But when the set is empty,
what should desiredset be?

Depending upon how your data is set up, I can think of at least a
couple of ways you can represent the empty set without using a select from
sysdummy. desiredset = (0) or desiredset = (null) Basically, use an SQL
acceptable value that does not occur in your data.

Sincerely,

Dave Clark