Menu
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?
761
Yes, you can alias multiple columns at a time in the same query. It is advisable to always include quotes around the new alias. Quotes are required when the alias contains spaces, punctuation marks, special characters or reserved keywords. It is simply more convenient to always include quotes around the new alias.
SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of that query. An alias is created with the AS keyword.
An alias/table prefix is not allowed in a COUNT(*). It is allowed in a SELECT list. That is, you cannot do this: SELECT COUNT(t.)
The basic syntax of a table alias is as follows. SELECT column1, column2.... FROM table_name AS alias_name WHERE [condition];
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)
)
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
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
