Re: Idiomatic SQL empty set?

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. (Indeed, this is
WHY I started the thread. My code had been using null in place of the
empty set since pretty much forever, and I never realized it wouldn't
work until today, when an empty set actually came my way and the
program blew up.)

As for coming up with a different sentinel value: I appreciate the
suggestion, but it's still relatively ugly (because the values would
have to be exotic, not just zero or blank, and I'm still not 100% sure
I can *guarantee* that they won't be contained in the data) and I
would need to have several of these, of various data types. If
myfield2 is numeric, the empty string won't work due to conversion
error, for example.

So suddenly I have to keep track of types, when I really wanted just
one thing I could use for any type. And I might not even be able to
guarantee that the sentinels aren't in the data. No thanks.

Any other ideas?

John Y.