SQL for returning 1or 0 if a row exists

[Charles Wilt <charles.wilt@xxxxxxxxx>]

You've probably seen SQL that check for the existence of at least 1
record with a given key:

select 1 into :recordExists from sysibm/sysdummy1
where exists(select 1 from myfile
                    where mykey = :someKeyValue
                   )

The above works great if a record does exist.  However, in order to
handle the case where a record doesn't exist you'd need to do one of
the following:
1) set recordExists to 0 before the SQL statement, if no records are
found the value is unchanged
2) check SQLSTATE or SQLCODE to see if no rows were returned, if so
then the record wasn't found

I've always thought it'd be nice if you could have a statement that
explicity returned 1 or 0.  About 5 years ago, I posted this statement
in the newsgroup:

select
  case tbl.flag
   when 1 then '1'
   else '0'
  end
into :recordExists
from ( select 1 as flag
       from sysibm/sysdummy1
       where exists (select 1 from myfile
                     where mykey = :someKeyValue
                    )
     ) as tbl right join sysibm/sysdummy1 on 1=1


Another statement I just came up with is:
select coalesce((select 1 from sysibm/sysdummy1
                       where exists (select 1 from myfile
                                          where mykey = :somekeyValue
                                         )
                      ),0)  into :recordExists
from sysibm/sysdummy1;

I don't have a proper benchmarking environment but Visual Explain
shows the two alternatives as having comparable performance both of
which are slightly slower than the original.

If I get a chance, I might try doing at least some benchmarking.

In the meantime, I just wanted to make this past available in the archives.

Charles Wilt