Correct CASE structure in SQL Server

Question

Correct CASE structure in SQL Server

Navigation

#1 by (10 votes)

4

I have the following query:

SELECT 
*,
CASE PORCENTAJE_CRUCE
WHEN PORCENTAJE_CRUCE >= 80.00 AND PORCENTAJE_CRUCE <= 85.99 THEN 2000
WHEN PORCENTAJE_CRUCE >= 86.00 AND PORCENTAJE_CRUCE <= 90.99 THEN 3000
WHEN PORCENTAJE_CRUCE >= 91.00 THEN 5000
ELSE 0
END AS PAGO_INCENTIVO
FROM #AR_PORCENTAJE

and the following error:

Incorrect syntax near '>'.

I guess the condition I'm putting after the WHEN is incorrect, how would be the correct way to add these conditions after WHEN ?

sql sql-server

asked by ARR 27.08.2018 в 17:16

source

1 answer

String Separator in Java Advantages of using const on var in JavaScript

score 10 · Accepted Answer

The expression CASE in SQL Server has 2 forms, in the first one you compare a column with equal conditions:

CASE Columna
     WHEN valor1 THEN resultado1
     WHEN valor2 THEN resultado2
     …
     ELSE resultadox
END

Or if you want more elaborate comparisons that are not simply the equality of a single column with some value:

CASE
    WHEN columna BETWEEN valor1 AND valor2 THEN resultado
    WHEN ….
END

Basically, in your case you just have to remove PORCENTAJE_CRUCE immediately after CASE :

SELECT 
*,
CASE 
    WHEN PORCENTAJE_CRUCE >= 80.00 AND PORCENTAJE_CRUCE <= 85.99 THEN 2000
    WHEN PORCENTAJE_CRUCE >= 86.00 AND PORCENTAJE_CRUCE <= 90.99 THEN 3000
    WHEN PORCENTAJE_CRUCE >= 91.00 THEN 5000
ELSE 0
END AS PAGO_INCENTIVO
FROM #AR_PORCENTAJE;