Problem with field not equal to null in case statement

4305 views sql
2

So I have EXISTS in huge query which looks like this:

EXISTS(
   SELECT 
      * 
   FROM 
      ExistTable
   WHERE
      ExTableFieldA = @SomeGuid AND
      ExTableFieldB = MainTableFieldB AND
      ExTableFieldA <> (
                         CASE
                           WHEN MainTableFieldZ = 10 THEN MainTableFieldYYY
                           ELSE NULL
                         END
                       )
)

The problem comes from ELSE part of CASE statement, this ExTableFieldA <> NULL will be always false. I could easily write another parameter @EmptyGuid and make it equal to '00000000-0000-0000-0000-000000000000' and everything will work but is this the best approach ?

Pretty much I want to execute another check into the exist for the small size of the records which return the "main" query.

answered question

2 Answers

0

How about removing the case and just using boolean logic?

WHERE ExTableFieldA = @SomeGuid AND
      ExTableFieldB = MainTableFieldB AND
      (MainTableFieldZ <> 10 OR ExTableFieldA <> MainTableFieldYYY)

I would also recommend that you qualify the column names by including the table alias.

Note: This does assume that MainTableFieldZ is not NULL. If that is a possibility than that logic can easily be incorporated.

posted this
6

ELSE NULL is implied even if you don't list it, but you could use ISNULL here.

ISNULL(ExTableFieldA,'') <> (
                         CASE
                           WHEN MainTableFieldZ = 10 THEN MainTableFieldYYY
                           ELSE ''
                         END
                       )

You may need to use some other value like 9999 instead of ''

posted this

Have an answer?

JD

Please login first before posting an answer.