How to alias a list of values in SQL

Question

I need to see if any of a set of columns contains a value in a list.

E.G

...

SELECT * 
FROM Account
WHERE
  NOT (
    AccountWarningCode1     IN (02, 05, 15, 20, 21, 24, 31, 36, 40, 42, 45, 47, 50, 51, 52, 53, 55, 56, 62, 65, 66, 78, 79, 84, 110, 119, 120, 121, 125, 202)
    OR AccountWarningCode2  IN (02, 05, 15, 20, 21, 24, 31, 36, 40, 42, 45, 47, 50, 51, 52, 53, 55, 56, 62, 65, 66, 78, 79, 84, 110, 119, 120, 121, 125, 202)
    OR AccountWarningCode3  IN (02, 05, 15, 20, 21, 24, 31, 36, 40, 42, 45, 47, 50, 51, 52, 53, 55, 56, 62, 65, 66, 78, 79, 84, 110, 119, 120, 121, 125, 202)
  ...
  )

The above does work, but what i'd like to do instead is alias the list some how so I don't repeat myself quite as much.

For example (this doesn't actually work)

WITH bad_warnings AS (02, 05, 15, 20, 21, 24, 31, 36, 40, 42, 45, 47, 50, 51, 52, 53, 55, 56, 62, 65, 66, 78, 79, 84, 110, 119, 120, 121, 125, 202)

SELECT * 
FROM Account
WHERE
  NOT (
    AccountWarningCode1     IN bad_warnings
    OR AccountWarningCode2  IN bad_warnings
    OR AccountWarningCode3  IN bad_warnings
  ...
  )

Is this possible in T-SQL?

Answer 1

Your second version is actually close. You can use a common table expression:

WITH bad_warnings(code) AS(
    SELECT * FROM(VALUES
        ('02'), ('05'), ('15'), ('20'), ('21'), ('24'),
        ('31'), ('36'), ('40'), ('42'), ('45'), ('47'),
        ('50'), ('51'), ('52'), ('53'), ('55'), ('56'),
        ('62'), ('65'), ('66'), ('78'), ('79'), ('84'),
        ('110'), ('119'), ('120'), ('121'), ('125'), ('202')
    ) a(b)
)
SELECT *
FROM Account
WHERE
    NOT (
         AccountWarningCode1 IN (SELECT code FROM bad_warnings)
        OR AccountWarningCode2 IN (SELECT code FROM bad_warnings)
        OR AccountWarningCode3 IN (SELECT code FROM bad_warnings)
    )

Answer 2

This is the way to define a derived table with your values as CTE.

WITH bad_warnings AS 
(SELECT val FROM (VALUES(02),(05),(15),(20),(21),(24),(31),(36),(40),(42),(45),(47),(50),(51),(52),(53),(55),(56),(62),(65),(66),(78),(79),(84),(110),(119),(120),(121),(125),(202)) AS tbl(val)
)
SELECT * 
FROM bad_warnings

You can use this as any table in your query.

Your check would be something like

WHERE SomeValue IN(SELECT val FROM badWarnings)

With NOT IN you would negate this list