CODEWITHSUNDEEP
mysqlsql-server
Alec Smythe
Alec Smythe

Reputation: 810

using complex keys in MSSQL IN clause

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

Upvotes: 1

Views: 248

Answers (4)

Lukasz Szozda
Lukasz Szozda

Reputation: 176264

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453920

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.

Upvotes: 3

Rahul
Rahul

Reputation: 77936

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';

Upvotes: 0

Related Questions