Assign value to variable based on condition (value of other variable) in PLSQL

Question

I want to assign value to a variable based on if another variable is null or not. I know there is error in the same code i am giving below, but i tried like this and don't know how i can get it done. although it gives clear picture of what i want to do.

declare 
lv_var1 varchar2(500);
lv_var2 varchar2(20);
ld_date date;

begin 
lv_var2 := 'sample value';
lv_var1 := case ld_date when not null 
                        then lv_var2
           end case;

dbms_output.put_line(lv_var1);         
end;

i want to assign the the value of lv_var2 into lv_var1 when ld_date is not null, else lv_var1 will be null.

The error i am getting is ORA-06550. This is probably because of the case statement, but what else can i use in place of it to get the result.

Please help. Thanks ...

Lalit Kumar B · Accepted Answer · 2016-02-17 11:16:42Z

You have a syntax error in the CASE expression, no need of CASE keyword with the END keyword while using it in SQL. END keyword is required in PL/SQL.

Using CASE as SQL:

SQL> SET serveroutput ON
SQL> DECLARE
  2    lv_var1 VARCHAR2(500);
  3    lv_var2 VARCHAR2(20);
  4    ld_date DATE;
  5  BEGIN
  6    ld_date:= SYSDATE;
  7    lv_var2 := 'sample value';
  8    lv_var1 :=
  9    CASE
 10    WHEN ld_date IS NOT NULL THEN
 11      lv_var2
 12    END;
 13    dbms_output.put_line(lv_var1);
 14  END;
 15  /
sample value

PL/SQL procedure successfully completed.

SQL>

Using CASE in PL/SQL:

SQL> SET serveroutput ON
SQL> DECLARE
  2    lv_var1 VARCHAR2(500);
  3    lv_var2 VARCHAR2(20);
  4    ld_date DATE;
  5  BEGIN
  6    ld_date := SYSDATE;
  7    lv_var2 := 'sample value';
  8    CASE
  9    WHEN ld_date IS NOT NULL THEN
 10      lv_var1    := lv_var2;
 11    END CASE;
 12    dbms_output.put_line(lv_var1);
 13  END;
 14  /
sample value

PL/SQL procedure successfully completed.

SQL>

The same could be written using IF in PL/SQL:

SQL> SET serveroutput ON
SQL> DECLARE
  2    lv_var1 VARCHAR2(500);
  3    lv_var2 VARCHAR2(20);
  4    ld_date DATE;
  5  BEGIN
  6    ld_date    := SYSDATE;
  7    lv_var2    := 'sample value';
  8    IF ld_date IS NOT NULL THEN
  9      lv_var1  := lv_var2;
 10    END IF;
 11    dbms_output.put_line(lv_var1);
 12  END;
 13  /
sample value

PL/SQL procedure successfully completed.

SQL>

Nick Koutsis · Accepted Answer · 2016-02-17 14:56:20Z

1

lv_var1 := case when ld_date is not null 
                        then lv_var2
           else null end case_;

answered Feb 17, 2016 at 14:56

Nick Koutsis

134 bronze badges

Comments

SkyWalker · Accepted Answer · 2016-02-17 11:31:06Z

0

lv_var1 := case when ld_date is not null 
                    then lv_var2
            end;

or

select nvl2(ld_date,lv_var2,null) into lv_var1 from dual;

edited Feb 17, 2016 at 11:31

answered Feb 17, 2016 at 11:17

SkyWalker

4942 silver badges7 bronze badges

1 Comment

Lalit Kumar B Over a year ago

No, it is still wrong. ELSE has nothing to do with OP's error. ELSE is optional, and not mandatory. The issue is syntax in PL/SQL.

mi_h · Accepted Answer · 2016-02-17 11:36:05Z

0

this should work:

declare 
lv_var1 varchar2(500);
lv_var2 varchar2(20);
ld_date date;
begin 
ld_date := null;  -- initialize the variable with null, or another value like sysdate
lv_var2 := 'sample value';
lv_var1 := case when ld_date is not null  -- case statement adapted
                        then lv_var2
           end;

dbms_output.put_line(lv_var1);         
end;

answered Feb 17, 2016 at 11:36

mi_h

545 bronze badges

Comments

Avrajit Roy · Accepted Answer · 2016-02-17 11:44:26Z

0

Hey you can try nvl2 function which can also suffice your requirement. Hope this helps.

SET serveroutput ON;
DECLARE
  lv_var1 VARCHAR2(500);
  lv_var2 VARCHAR2(20);
  ld_date DATE:= NULL;
BEGIN
  lv_var2 := 'sample value';
  SELECT nvl2(ld_date,lv_var2,NULL) INTO lv_var1 FROM dual;
  dbms_output.put_line(lv_var1);
END;

answered Feb 17, 2016 at 11:44

Avrajit Roy

3,3131 gold badge16 silver badges25 bronze badges

Collectives™ on Stack Overflow

Assign value to variable based on condition (value of other variable) in PLSQL

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related