CODEWITHSUNDEEP
sqlt-sqlsetalias
Luke
Luke

Reputation: 5708

How to alias a list of values in SQL

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?

Upvotes: 5

Views: 2490

Answers (4)

Rahul
Rahul

Reputation: 77866

Is this possible in T-SQL?

Yes, either use a table variable or a temporary table. Populate those inlist data in table variable and use it as many places within your procedure you want.

Example:

declare @inlist1 table(elem int);
insert into @inlist1
select 02
union
select 05
union
select 15
union
select 20
union
select 21
union
select 24

Use it now

WHERE
  NOT (
    AccountWarningCode1     IN (select elem from @inlist1)

(OR)

You can as well perform a JOIN operation saying

FROM Account a
JOIN @inlist1 i ON a.AccountWarningCode1 = i.elem

Upvotes: 2

Dan Bracuk
Dan Bracuk

Reputation: 20794

You can do it like this:

with bad_warnings as 
(select '02'
union
select '15'
etc
)
select * from account
where not
(AccountWarningCode1 IN (SELECT code FROM bad_warnings
etc)

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

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

Upvotes: 5

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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

Upvotes: 2

Related Questions