CASE WITH IN does not work

Asked

Viewed 222 times

2

I’m willing to do this search below:

DECLARE @codccu VARCHAR(10);

SET @CodCcu = '63'
  SELECT E.codepi, 
             E.numcad, 
             Max(E.datent)            AS ENTREGA, 
             Max(E.datent) + P.diaval AS VALIDADE, 
             CASE 
               WHEN Max(E.datent) + P.diaval <= Getdate() THEN 'VENCIDO' 
               ELSE 'OK' 
             END              AS SITUACAO 
      FROM   r096die E, 
             r096epi P 
      WHERE  E.numcad = 241 
             AND 
                CASE  
                    WHEN @codccu IN ('911003','63') THEN E.codepi IN (1,6,23,24) 
                    ELSE E.codepi IN (1,23)
                END                         
             AND P.codepi = E.codepi 
      GROUP  BY E.codepi, 
                E.numcad, 
                P.diaval

Since, I want to get the codEpi (1,6,23,24) if the cost centre is ('911003','63') , if I don’t want you to list the codes with the numbers (1,23)

But it presents the following error:

Incorrect syntax next to keyword 'IN'.. Error 156. SQLSTATE 42000. Severity 15. Msgstate 1. Line 18.

That this is the line:

WHEN @codccu IN ('911003','63') THEN E.codepi IN (1,6,23,24)

I already searched and I can not find where I am missing in this script, I have tried also as follows:

AND E.codepi =
                CASE  
                    WHEN @codccu IN ('911003','63') THEN IN (1,6,23,24) 
                    ELSE  IN (1,23)
                END

but presents the same error.

sql database sql-server

  • If you replace IN (1,6,23,24) for 1,6,23,24 works! Actually you can’t have multiple results on the same line. The result is one for each line!

  • Thanks help @Marconi, but this way it doesn’t work, for the reason of the comma it will be lose not knowing which one to look for.

  • I forgot to quote!

1 answer

3


You may not use a IN as a result of a CASE. Replace with a structure with OR:

WHERE  E.numcad = 241 
  AND ((@codccu IN ('911003','63') AND E.codepi IN (1,6,23,24))
   OR (E.codepi IN (1,23)))        
  AND P.codepi = E.codepi

Browser other questions tagged sql database sql-server

You are not signed in. Login or sign up in order to post.