CODEWITHSUNDEEP
sqlsql-server
mybirthname
mybirthname

Reputation: 18127

Problem with field not equal to null in case statement

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.

Upvotes: 0

Views: 340

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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.

Upvotes: 3

S3S
S3S

Reputation: 25112

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 ''

Upvotes: 0

Related Questions