So I have EXISTS
in huge query which looks like this:
x
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.
Advertisement
Answer
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.