RE: Idiomatic SQL empty set?

In order to use "null" here you need to tell the system its data type using cast.

select myfield1 from mylib.myfile
where myfield2 in ( values( cast( null as Char(1)))


select myfield1 from mylib.myfile
where myfield2 in ( cast( null as Char(1)) )


In this case you would replace the "char(1)" with the data type of "myfield2".

Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of John Yeung
Sent: Friday, August 4, 2017 12:46 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Idiomatic SQL empty set?

Is there an idiomatic, or at least common, expression that returns the
empty set in DB2 for i? (Or is that Db2 for i now?)

For example, maybe something like

select * from sysibm.sysdummy1 where 1 > 2

In particular, I'm interested in an expression that can be used with IN, as in

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

I was really hoping that it would simply be (), but that doesn't work.

So for now, the larger statement looks like

select myfield1 from mylib.myfile
where myfield2 in (select * from sysibm.sysdummy1 where 1 > 2)

Surely there's something better?

(In case you haven't guessed, the set of desired values for myfield2
is dynamically generated and won't always be empty.)

John Y.