CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
Ken
Ken

Reputation: 312

SQL Server - Using Combined Column in WHERE Clause

I'm having trouble using a combined column in my WHERE clause. I'm trying to combine two columns and do a WHERE x IN (a,b,c) to filter the rows.

Here's my data:

Tag      Param
------   -----
TI-123   LL
TI-123   LO
FI-321   LO
FI-321   BAD

Here's what I'm trying:

SELECT [Tag] + '.' + [Param] AS 'TagParam' 
   FROM Tags
   WHERE 'TagParam' IN ('TI-123.LL', 'TI-123.LO')

The results never match the WHERE clause and always come up empty. I could keep the rows separate and use WHERE with a lot of ANDs and ORs, but it's much harder to read. I'm going to have around 100 items in the IN() statement.

Upvotes: 0

Views: 74

Answers (2)

paparazzo
paparazzo

Reputation: 45096

This is how I would write that 

SELECT [Tag] + '.' + [Param] AS 'TagParam' 
FROM Tags
WHERE Tag = 'TI-123'
  AND Param IN ('LL', 'LO')

This make it sargable (can use indexes).

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You cannot use a table alias defined in a SELECT in the corresponding WHERE. Putting single quotes around things just confuses the issue, turning things into string constants.

Repeat the expression:

SELECT ([Tag] + '.' + [Param]) AS TagParam
FROM Tags
WHERE ([Tag] + '.' + [Param]) IN ('TI-123.LL', 'TI-123.LO');

If you don't want to repeat the expression, you can use a subquery, CTE, or lateral join (the apply keyword). But your expression is simple enough.

Upvotes: 4

Related Questions