Sql statement for mutually exclusive events

I am trying to run an sql statement on an iSeries that will output retuls based on a type pa开发者_如何学JAVArameter I pass in.

Just say mytable has a field called field1. field1 contains Y,N and NULL values.

A type of 'Y' should return just 'Y' values. A type of 'N' should return not 'Y' values. (ie. Null, N and any other junk in the field)

I tried this...

        select *
          from mytable
          where field1 in case when :type = 'Y' then 'Y'
               else (select field1 from mytable where field1 <> 'Y') end

However, this does not work.

I believe the logic you are looking for is this:

SELECT *
FROM myTable
WHERE (:type = 'Y' AND field1 IS NOT null AND field1 = 'Y')
   OR (:type <> 'Y' AND (field1 IS null OR field1 <> 'Y'))

(keep in mind the fact that short-circuit logic is not garuanteed with SQL...)

Remember that null doesn't really compare to anything, and it's best to call out the fact that you actually want it (in the second case).