Home / Questions / 517249
Accepted
Henriquez10
Asked: 2022-07-26 16:30:17 +0800 CST 2022-07-26 16:30:17 +0800 CST

How to bind a case to my statement using PL/SQL?

  • 772

Anyone who can give me a recommendation would appreciate it.

I have made this query

SELECT SUM(CASE
WHEN PPL.TIPO = '1' THEN PPL.CANTIDAD*PPL.PRECIO END) AS TOTAL
-
SUM(CASE
WHEN PPL.TIPO = '2' THEN PPL.CANTIDAD*PPL.PRECIO END) AS TOTAL,
SUM(CASE
WHEN PPL.TIPO = '1' THEN PPL.CANTIDAD*PPL.PRECIO END) AS TOTAL,
SUM(CASE
WHEN PPL.TIPO = '2' THEN PPL.CANTIDAD*PPL.PRECIO END) AS TOTAL
FROM DETALLE PP
INNER JOIN PRODUCTO  PPL ON
PP.ID= PPL.ID
AND PP.CODIGO = PPL.CODIGO
AND PP.NRO_PROD= PPL.NRO_PROD

Basically I need to do the sum of the total of type = '1' minus the sum of the total of type = '2' and in the others I only do sums of type case.

But when executing the statement in Oracle SQL Developer, it displays the following error:

*ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected" *Cause:
Action: Error at line: 44, column: 1

plsql

1 Answers

  1. Best Answer

    I did some testing to find the problem and it seems to be in the alias of the first SUM. Well, the alias is for the difference. I put it like this so that it is noticed: The difference of the first two SUMis DIFFERENCE . Then the aliases for the SUMfollowing are TOTTIPO1 and TOTTIPO2 . It should work for you.

    SELECT SUM(CASE WHEN PPL.TIPO = '1' THEN PPL.CANTIDAD*PPL.PRECIO END) - SUM(CASE WHEN PPL.TIPO = '2' THEN PPL.CANTIDAD*PPL.PRECIO END) AS DIFERENCIA,
SUM(CASE WHEN PPL.TIPO = '1' THEN PPL.CANTIDAD*PPL.PRECIO END) AS TOTTIPO1, 
SUM(CASE WHEN PPL.TIPO = '2' THEN PPL.CANTIDAD*PPL.PRECIO END) AS TOTTIPO2
FROM DETALLE PP
INNER JOIN PRODUCTO  PPL ON
PP.ID= PPL.ID
AND PP.CODIGO = PPL.CODIGO
AND PP.NRO_PROD= PPL.NRO_PROD
    • 0