Using If Else in Oracle

Defining a variable using an if statement Oracle SQL Developer

You could use PL/SQL anonymous block which would be simpler:

SET SERVEROUTPUT ON

DECLARE
    v_report_month   NUMBER := 1;
    v_report_year    NUMBER := 2020;
    v_start_year     NUMBER;
BEGIN
    v_start_year :=
        CASE
            WHEN v_report_month > 1 THEN
                v_report_year - 2
            ELSE v_report_year - 3
        END;
    dbms_output.put_line('Start year: '||v_start_year);
END;
/

Start year: 2017

PL/SQL procedure successfully completed.

The same CASE expression could be written using IF-THEN-ELSE:

IF v_report_month > 1 THEN
    v_start_year := v_report_year - 2;
ELSE
    v_start_year := v_report_year - 3;
END IF;

If you want to leave the variables which you have defined in SQL*Plus, then you could do it this way:

define v_report_month = 1
define v_report_year = 2020

set serveroutput on
DECLARE
    v_report_year   NUMBER;
    v_start_year    NUMBER;
BEGIN
    v_start_year :=
        CASE
            WHEN &v_report_month > 1 THEN
                &v_report_year - 2
            ELSE &v_report_year - 3
        END;
    dbms_output.put_line(v_start_year);
END;

2017

PL/SQL procedure successfully completed.

IF ELSE condition on ORACLE

A case expression in SQL is an equivalent to IF statement in other languages:

CASE WHEN condition THEN value1 [ ELSE value2 ] END

I guess that you are looking for something like this:

SELECT distinct
  A, B, C, D, E,
  SUM(F+G+H) AS "FGH",
  SUM(I -(FGH)) AS "I",
  CASE WHEN CODE='fuu' THEN bar ELSE 0 END As "What I need"
FROM
  TABLE
WHERE 
  A=20
GROUP BY
  A, B, C,  D, E, "What I need"

How to use IF THEN ELSE statement in where clause in Oracle?

You need to bundle them

where (
(pcustomer_id is null 
    and c.customer_id is null)
or 
(pcustomer_id is not null 
    and c.customer_id = pcustomer_id)
)

Use IF statement in Oracle SQL

IF is a PL/SQL statement; you cannot use it in SQL.

Just use AND and OR:

SELECT *
FROM   TABLE_A a
       INNER JOIN TABLE_B b
       ON a.user_id = b.user_id
WHERE  (   b.last_name IS NULL
       AND (  a.user_id in (1, 2, 3)
           OR a.user_name in ('John', 'Mark', 'Lucas')
       )
OR     b.last_name = 'DOE'

Note: In Oracle, an empty string and NULL are identical.

Alternative for IF statement in plsql select Into

In my opinion, you're trying to do it wrong.

You can't and shouldn't have separate variables for all fruits. OK, now you have only two - v_apple_id and v_mango_id. What when bananas come? Will you create yet another v_banana_id and modify the whole code of your procedure? Lucky you, you'll do it in a matter of an hour or two. And tomorrow, you'll get v_peach_id and v_pear_id. What then?

As I said - completely wrong approach.

---

I don't know what v_block represents (you never posted much information about the whole problem), so here's code as it should look like (again, in my opinion):

CREATE OR REPLACE PROCEDURE p_fruit (
   par_fruit_id  IN table_fruits.fruit_id%TYPE)
IS
   v_count  NUMBER;
BEGIN
   SELECT COUNT (*)
     INTO v_count
     FROM table_fruits
    WHERE fruit_id = par_fruit_id;
END;
/

In other words: create a procedure which will accept fruit ID as a parameter and use it later in its code. That's general approach. It might need to be changed, but - that depends on what you're really trying to do. Maybe it should just be converted to a function which returns number of e.g. apples ... can't tell at the moment.

---

Related Topics