Using case to choose between two clauses in where where (Oracle)
im create a function and i need to choose between two sentences depending on the parameter value (PTIPO)
Below is my code
SELECT SUM(
CASE PTIPO
WHEN 0 THEN (CASE WHEN (A.NT = 0) THEN A.VALOR ELSE 0 END)
WHEN 1 THEN (CASE WHEN (A.NT = 1) THEN A.VALOR ELSE 0 END)
WHEN 2 THEN (CASE WHEN (A.NT = 1) THEN A.VALOR ELSE -A.VALOR END)
END)
INTO nresp
FROM mov_caja a
JOIN enc_movp b ON a.docid = b.docid
JOIN c_caja c ON a.cajaid = c.cajaid
WHERE c.cajaid = pcajaid AND(
CASE WHEN(PTIPO = 2) --this is what causes the issue.
THEN
b.cod_compro = ncompini OR (b.fecha_mov BETWEEN pfecha_ini AND pfecha_fin)
ELSE
b.cod_compro != ncompini AND b.fecha_mov BETWEEN pfecha_ini AND pfecha_fin
END);
The "keyword is missing" error, but I know the problem is the last CASE. How else can I choose between offers. Is this the correct case or should I use a different expression?
+3
source to share
2 answers
A case can return a value, but not an expression. This should do the trick, although I feel like it could be even simpler:
SELECT SUM(
CASE PTIPO
WHEN 0 THEN (CASE WHEN (A.NT = 0) THEN A.VALOR ELSE 0 END)
WHEN 1 THEN (CASE WHEN (A.NT = 1) THEN A.VALOR ELSE 0 END)
WHEN 2 THEN (CASE WHEN (A.NT = 1) THEN A.VALOR ELSE -A.VALOR END)
END)
INTO nresp
FROM mov_caja a
JOIN enc_movp b ON a.docid = b.docid
JOIN c_caja c ON a.cajaid = c.cajaid
WHERE
c.cajaid = pcajaid AND
(
(PTIPO = 2 and (b.cod_compro = ncompini OR b.fecha_mov BETWEEN pfecha_ini AND pfecha_fin))
OR
(PTIPO <> 2 and (b.cod_compro != ncompini AND b.fecha_mov BETWEEN pfecha_ini AND pfecha_fin))
)
+3
source to share