CODEWITHSUNDEEP
sqlsql-servert-sqlcase
user3115933
user3115933

Reputation: 4443

How to re-write my CASE expression to avoid this specific error message?

I am using SQL Server 2014. I have the following T-SQL query running against a table (T1). An extract of table T1 is given below:

ID   N1   N2   N3   N4   N5   N6
1    2    10   12   25   29   30
2    10   13   23   24   35   39
3    1    20   23   26   32   40
4    5    9    11   12   28   35
...

Expected output:

I want the output of my query to find if any of the values [N1] to [N6] of the current [ID] are present in any of the values at 2 [ID] levels above.

To simplify, the query needs to find out if the values ([N1] to [N6]) at [ID] = 4 exists in the values at [ID]-2; that is at [ID] = 2

ID   N1   N2   N3   N4   N5   N6   N1_ID-2   N2_ID-2   N3_ID-2   N4_ID-2   N5_ID-2   N6_ID-2
1    2    10   12   25   29   30     0         0         0         0         0         0
2    10   13   23   24   35   39     0         0         0         0         0         0
3    1    20   23   26   30   40     0         0         0         0         1         0
4    5    9    11   13   28   35     0         0         0         1         0         1
...

My query currently stands as follows:

USE MyDatabase
    
SELECT *,        
    (CASE WHEN [N1] IN (SELECT [N1], [N2], [N3], [N4], [N5], [N6] FROM [T1] WHERE [ID] = [ID] -2) THEN 1 ELSE 0 END) AS [N1_ID-2],
    (CASE WHEN [N2] IN (SELECT [N1], [N2], [N3], [N4], [N5], [N6] FROM [T1] WHERE [ID] = [ID] -2) THEN 1 ELSE 0 END) AS [N2_ID-2],
    (CASE WHEN [N3] IN (SELECT [N1], [N2], [N3], [N4], [N5], [N6] FROM [T1] WHERE [ID] = [ID] -2) THEN 1 ELSE 0 END) AS [N3_ID-2],
    (CASE WHEN [N4] IN (SELECT [N1], [N2], [N3], [N4], [N5], [N6] FROM [T1] WHERE [ID] = [ID] -2) THEN 1 ELSE 0 END) AS [N4_ID-2],
    (CASE WHEN [N5] IN (SELECT [N1], [N2], [N3], [N4], [N5], [N6] FROM [T1] WHERE [ID] = [ID] -2) THEN 1 ELSE 0 END) AS [N5_ID-2],
    (CASE WHEN [N6] IN (SELECT [N1], [N2], [N3], [N4], [N5], [N6] FROM [T1] WHERE [ID] = [ID] -2) THEN 1 ELSE 0 END) AS [N6_ID-2]        
FROM [T1]

Running the above set of code is giving me the following error message:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

How can I correct my code to avoid this error?

Upvotes: 0

Views: 65

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

You can use a self join and IN like this:

select t.*,
       (case when t.n1 in (tprev.n1, tprev.n2, tprev.n3, tprev.n4, tprev.n5, tprev.n6) then 1 else 0 end) n1_comp,
       (case when t.n2 in (tprev.n1, tprev.n2, tprev.n3, tprev.n4, tprev.n5, tprev.n6) then 1 else 0 end) as n2_comp,
       . . . 
from t left join
     t tprev
     on tprev.id = t.id - 2

Upvotes: 3

GSerg
GSerg

Reputation: 78185

select
  t1.*,
  case when t1.N1 in (t1_old.N1, t1_old.N2, t1_old.N3, t1_old.N4, t1_old.N5, t1_old.N6) then 1 else 0 end as [N1_ID-2],
  case when t1.N2 in (t1_old.N1, t1_old.N2, t1_old.N3, t1_old.N4, t1_old.N5, t1_old.N6) then 1 else 0 end as [N2_ID-2],
  ...
from
  t1
  left join t1 as t1_old on t1_old.id = t1.id - 2

Upvotes: 1

Related Questions