TheQuestioner TheQuestioner - 5 months ago 30
SQL Question

Field IS NULL in IIF()

I have two types,

TypeA
and
TypeB
.

TypeA
has two Series -
Series1
and
Series2


TypeB
has one Series -
Series1


Below is the query I managed to come up with:

SELECT
*
FROM
TypesTable
WHERE
Series1 = 'A3bBa#$#0sB2'
AND
Series2 = IIF(Type != 'TypeB', 'vH2f##gYtL&', NULL);


I am having problem with the
Series2 = IIF(...)
part

How can I tell my query that

if
Type = TypeA
,
Series2
is equal to given
Series2
value by user

if
Type = TypeB
,
Series2
is NULL

I am having a hard time with the
IIF
since if
Type == 'TypeB'
Series2
should be
Series2 IS NULL
and not
Series2 = NULL

Answer

Extend condition check in the WHERE clause.

SELECT
   *
FROM
    TypesTable
WHERE
    Series1 = 'A3bBa#$#0sB2'
AND
    ((Type = 'TypeA' AND Series2 = 'vH2f##gYtL&') OR (Type = 'TypeB' AND Series2 IS NULL))