using complex keys in MSSQL IN clause

Question

I have the following query that works in MySQL:

SELECT 'DEMO' client, COUNT(*) n, SUM(PYMT_Total_Paid) actual
FROM Payments 
WHERE (PYMT_CLIENT1,PYMT_CLIENT2) IN (('DEMO','SL'))
  AND PYMT_DTEPYD ='20150825' 
  AND PYMT_MISC IN ('PY','RC','ER','RG','SP','BN','BS','SB')
  AND PYMT_BEEN_REVERSED != 'Y'

When I run it in MSSQL it fails with this message:

An expression of non-boolean type specified in a context where a condition
is expected, near ','.

If I remove (PYMT_CLIENT1,PYMT_CLIENT2) IN (('DEMO','SL')) AND from the query it runs fine. So it appears MSSQL expects a different syntax than MySQL when searching on composite keys using an IN clause.

Any pointers on where to find the correct syntax or what the correct syntax is would be highly appreciated. Thanks!

EDIT

The case I provided is oversimplified. Solution should also works if the query would have several pairs in the IN clause, e.g. ... IN (('DEMO","SL"),("ED","AUTO"),("ED","PHOTO"))

The case I provided is oversimplified. More typically the query would have several pairs in the IN clause, e.g. ... IN (('DEMO","SL"),("ED","AUTO"),("ED","PHOTO")). Does that change the advice below? — Alec Smythe, Aug 25 '15 at 21:34

score 3 · Answer 1 · answered Aug 25 '15 at 20:33

3

You could also change

(PYMT_CLIENT1,PYMT_CLIENT2) IN (('DEMO','SL'))

to

EXISTS (SELECT PYMT_CLIENT1,PYMT_CLIENT2 INTERSECT SELECT 'DEMO','SL')

This treats nulls differently from combining equality predicates but no difference in your case as the two constants on the right aren't null.

answered Aug 25 '15 at 20:33

Martin Smith

438,706
87
741
845

1

I extended your solution to handle case `IN (('DEMO","SL"),("ED","AUTO"),("ED","PHOTO"))`. [See](http://stackoverflow.com/questions/32213420/using-complex-keys-in-mssql-in-clause/32213450#32213450) – Lukasz Szozda Aug 25 '15 at 23:15

score 2 · Accepted Answer · edited May 23 '17 at 11:44

2

SQL Server does not support Row Value Constructor.

Similiar case UPDATE tab SET (a,b) = ('Yes', 'No'). Use T-SQL version

Solutions:

1) Use AND instead when condition is simple

(PYMT_CLIENT1,PYMT_CLIENT2) IN (('DEMO','SL'))
/* becomes */
PYMT_CLIENT1 = 'DEMO' AND PYMT_CLIENT2 = 'SL'

2) Second case IN (('DEMO","SL"),("ED","AUTO"),("ED","PHOTO")) can be unwrapped like:

  WHERE 
    CASE 
      WHEN PYMT_CLIENT1 = 'DEMO' AND PYMT_CLIENT2 = 'SL'    THEN 1
      WHEN PYMT_CLIENT1 = 'ED'   AND PYMT_CLIENT2 = 'AUTO'  THEN 1
      WHEN PYMT_CLIENT1 = 'ED'   AND PYMT_CLIENT2 = 'PHOTO' THEN 1
      ELSE 0
    END = 1

2') Move condition from WHERE to INNER JOIN and join with Derived Table:

SELECT 
    'DEMO'               AS  client,
    COUNT(*)             AS  n,
    SUM(PYMT_Total_Paid) AS actual
FROM Payments p
INNER JOIN (VALUES ('DEMO', 'SL'), ('ED', 'AUTO'), ('ED', 'PHOTO')) AS x(c1, c2)
ON p.PYMT_CLIENT1 = x.c1 AND p.PYMT_CLIENT2 = x.c2
WHERE
   (...)

2'') Combine Martin Smith solution with Derived Tables

WHERE
   EXISTS 
   (
       SELECT PYMT_CLIENT1,PYMT_CLIENT2
       INTERSECT
       SELECT c1, c2 
       FROM (VALUES ('DEMO', 'SL'), ('ED', 'AUTO'), ('ED', 'PHOTO')) AS X(c1, c2)
   )

edited May 23 '17 at 11:44

Community

1
1

answered Aug 25 '15 at 20:28

Lukasz Szozda

162,964
23
234
275

I guess I could generalize this by eliminating the IN clause and just building a bunch of OR statements. Ugly, but I guess that would work, e.g. ((PYMT_CLIENT1 = 'DEMO' AND PYMT_CLIENT2 = 'SL') OR (PYMT_CLIENT1 = 'ED' AND PYMT_CLIENT2 = 'AUTO') OR (PYMT_CLIENT1 = 'ED' and PYMT_CLIENT2 = 'PHOTO')) – Alec Smythe Aug 25 '15 at 21:42
@AlecSmythe Yes, your second case you can unwrap like you propose. I will think a little about more concise solution. – Lukasz Szozda Aug 25 '15 at 21:53
1

Or if they are stuck on 2005 `union all` can be used instead of `values`. – Martin Smith Aug 25 '15 at 23:20
@MartinSmith I know. Using WITH + change VALUES to UNION ALL I can generate at least 3 more possible answer :) Let's stop with what I've created so far. – Lukasz Szozda Aug 25 '15 at 23:24
thanks for all the work on this; much appreciated. I've learned quite a bit from these examples. – Alec Smythe Aug 26 '15 at 01:39

score 0 · Answer 3 · answered Aug 25 '15 at 20:30

0

Modify your query a bit like

SELECT 'DEMO' as client, 
COUNT(*) as n, 
SUM(PYMT_Total_Paid) as actual 
FROM Payments 
WHERE PYMT_CLIENT1 IN ('DEMO','SL')
AND PYMT_CLIENT2 IN  ('DEMO','SL') 
AND PYMT_DTEPYD = '20150825' 
AND PYMT_MISC IN ('PY','RC','ER','RG','SP','BN','BS','SB') 
AND PYMT_BEEN_REVERSED != 'Y';

answered Aug 25 '15 at 20:30

Rahul

76,197
13
71
125

Thanks ... but is this specific enough? I need for PYMT_CLIENT1 to match 'DEMO' and PYMT_CLIENT2 to match 'SL'. Your query would match a lot of other situations. Plus the point of the IN clause (which I did illustrate well) is that you might have multiple pairs you want to match on, as per the comment I just added to my original post. – Alec Smythe Aug 25 '15 at 21:39

score 0 · Answer 4 · answered Aug 25 '15 at 22:42

Here is the correct logic:

WHERE PYMT_CLIENT1 = 'DEMO' AND PYMT_CLIENT2 = 'SL' AND
      PYMT_DTEPYD = '20150825' AND
      PYMT_MISC IN ('PY', 'RC', 'ER', 'RG', 'SP', 'BN', 'BS', 'SB') AND 
      PYMT_BEEN_REVERSED <> 'Y';

The expression (a, b) in ((x, y)) is looking at the pair of values, not each value individually. Because there is only one value in the IN list, this is equivalent to just doing the comparisons separately.

using complex keys in MSSQL IN clause

4 Answers4