Re: Idiomatic SQL empty set?

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 08/04/2017
04:11:56 PM:

On Fri, Aug 4, 2017 at 3:45 PM, <dlclark@xxxxxxxxxxxxxxxx> wrote:

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.

Well, I already tried null before starting this thread, and got the
"Column or global variable NULL not found" message.

In that case, select 1 from sysdummy where 1 <> 2; won't work,
either, because that returns a null. If your desired set is empty then
you really don't want to execute the SQL. But, I presume you're trying to
pass back an empty result set. So, sounds like you have to fork the logic
-- if the desired set is not empty then execute one SQL statement else
execute an SQL statement that creates an empty result set: such as select
1 from sysdummy where 1 <> 2.

Sincerely,

Dave Clark