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
The short answer is yes, you can nest an if inside of swtich / case statement (or vice versa).
The if-else statement is used to choose between two options, but the switch case statement is used to choose between numerous options. If the condition inside the if block is false, the statement inside the else block is executed. If the condition inside the switch statement is false, the default statements are run.
The || operator is like | (§15.22. 2), but evaluates its right-hand operand only if the value of its left-hand operand is false.
C# Nested if/else StatementsWe can also use if or else inside another if or else statements. To understand this, let's see an example to print whether a number is the greatest or not by using one if inside another if. In the above example, the first expression i.e., if(z>x) is true.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With