create or replace FUNCTION macro_If_condition( p_1 varchar2 )
RETURN varchar2 SQL_MACRO is
v_sql varchar2(1000);
BEGIN
if p_1 is not null then
v_sql := q'[ select p_1 from dual ]' ;
else
v_sql := q'[ select 'NULLL' from dual ]' ;
end if;
RETURN v_sql;
END;
SELECT * FROM macro_If_condition( 'input1') t;
Op: NULLL --- its incorrect bcz we have passed input1 but don’t know why it went to ELSE condition
Can anyone explain why the IF condition is not working. You can try the above sample code.
CodePudding user response:
The value of string parameters in the body of SQL macros is always null
. You cannot use them to change the expression you return like this.
If you want to convert null
inputs into the string nulll
, you need to place this logic in the return string itself. For example, using a case
expression like this:
create or replace function macro_if_condition ( p_1 varchar2 )
return varchar2 sql_macro is
v_sql varchar2(1000);
begin
v_sql := q'[
select case when p_1 is not null then p_1 else 'nulll' end p1_val
from dual
]' ;
return v_sql;
end;
/
select * from macro_if_condition ( null );
P1_VA
-----
nulll
select * from macro_if_condition ( 'input' );
P1_VA
-----
input