Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

If condition inside case

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
like image 228
Alejandro Bastidas Avatar asked Jan 26 '13 14:01

Alejandro Bastidas


People also ask

Can you put an if statement inside a case?

The short answer is yes, you can nest an if inside of swtich / case statement (or vice versa).

Can we use if-else condition in switch case?

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.

What is || in if condition?

The || operator is like | (§15.22. 2), but evaluates its right-hand operand only if the value of its left-hand operand is false.

Can we use if inside switch case in C#?

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.


2 Answers

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
like image 172
Michael Berkowski Avatar answered Nov 14 '22 23:11

Michael Berkowski


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
like image 25
dani herrera Avatar answered Nov 14 '22 23:11

dani herrera