two case oracle sql-CodePudding

hello all i am trying to get two results with the same column in sql using case.

SELECT de.MONTH,
SUM(CASE WHEN cf.NOM_CF = 'COST' THEN de.MB END) AS out,
SUM(CASE WHEN cf.NOM_CF = 'ENTRY' THEN de.MB END) AS in
FROM DETALLE_ARCHIVO_IOT de
INNER JOIN ARCHIVO_IOT cab ON cab.ID_ARCHIVO_IOT = de.ID_ARCHIVO_IOT
INNER JOIN PLMN p ON p.CODE_PLMN = de.code
INNER JOIN CF cf ON cf.ID_CF = de.ID_CF
WHERE de.MONTH BETWEEN '202101' AND '202104' AND de.CALL_TYPE = 'GPRS' AND cab.ESTADO_DETALLE = 1
GROUP BY cf.NOM_CF, de.MONTH
ORDER BY de.MONTH;

but i get this error:

00000 - "FROM keyword not found where expected"

what am i doing wrong??

expected result:

 -------- ------ ----- 
|MONTH   |IN    |OUT  |
 -------- ------ ----- 
|202101  |  21  | 12  |
|202102  |  31  | 13  |
 -------- ------ -----

CodePudding user response：

IN is a reserved word; if you want to use it as an identifier then it needs to be quoted:

SELECT de.MONTH,
       SUM(CASE WHEN cf.NOM_CF = 'COST' THEN de.MB END) AS OUT,
       SUM(CASE WHEN cf.NOM_CF = 'ENTRY' THEN de.MB END) AS "IN"
FROM   DETALLE_ARCHIVO_IOT de
       INNER JOIN ARCHIVO_IOT cab ON cab.ID_ARCHIVO_IOT = de.ID_ARCHIVO_IOT
       INNER JOIN PLMN p ON p.CODE_PLMN = de.code
       INNER JOIN CF cf ON cf.ID_CF = de.ID_CF
WHERE  de.MONTH BETWEEN '202101' AND '202104' AND de.CALL_TYPE = 'GPRS' AND cab.ESTADO_DETALLE = 1
GROUP BY cf.NOM_CF, de.MONTH
ORDER BY de.MONTH;