If condition inside case

Question

I'm converting some MySQL 5.0 functions to Oracle 11g.

I need to place an IF inside a case but get an error (missing right parenthesis) this is my code

SELECT SUM(
    CASE PTIPO
      WHEN 0 THEN (SELECT IF(A.NT = 0) THEN A.VALOR ELSE 0 END IF FROM DUAL)
      WHEN 1 THEN (SELECT IF(A.NT = 1) THEN A.VALOR ELSE 0 END IF FROM DUAL)
      WHEN 2 THEN (SELECT IF(A.NT = 1) THEN A.VALOR ELSE -A.VALOR END IF FROM DUAL)
    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

Solution 1

IF / ELSE are used for flow control inside functions and prepared statments, rather than for assembling conditions in single statements.

Really you just need to nest another CASE inside using its other syntactic format CASE WHEN <condition> THEN <value>:

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

Solution 2

You can rewrite it as:

SELECT SUM(
    CASE 
      WHEN PTIPO = 0 and A.NT  = 0 THEN A.VALOR 
      WHEN PTIPO = 0 and A.NT <> 0 THEN 0
      WHEN PTIPO = 1 and A.NT  = 1 THEN A.VALOR 
      WHEN PTIPO = 1 and A.NT <> 1 THEN 0
      WHEN PTIPO = 2 and A.NT  = 1 THEN A.VALOR 
      WHEN PTIPO = 2 and A.NT <> 1 THEN -A.VALOR 
    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

Comments

[dani herrera]

De nada, Alejandro. Parece que esta pregunta he tenido mejor aceptación que la anterior ... ;)

[Aggie Jon of 87]

This style worked well for me. Thanks for the good and simple example and answer.