CODEWITHSUNDEEP
sqloracle-databaseplsql
thealchemist
thealchemist

Reputation: 407

Executing a statement with logical operator as a variable

I am trying to execute a SQL statement in Oracle: 

FLAG := CASE WHEN @LOOP_COUNT @TARGET_OPERATOR_1 1 THEN 'GREEN'
        ELSE 'RED' 
        END;

Here @Loop_count = some number and @Target_operator_1 is the comparision operator '>' or '<' or '>='

I can hard code all combinations inside the CASE expression, but I just wanted to check if its possible with dynamic SQL.

Cheers,

Upvotes: 1

Views: 652

Answers (1)

Aleksej
Aleksej

Reputation: 22949

If I understand well, you may need something like the following:

declare
    /* declare a variable to host a SQL query */
    vSQL    varchar2(1000);
    vResult varchar2(1000);
begin    
    /* build the SQL query as a string and save it into the variable */
    select 'select case ' || chr(13) || chr(10) ||
           listagg ( 'when ' || n1.num || op || n2.num || ' then ''' || n1.num || op || n2.num || '''', chr(13) || chr(10)) 
           within group ( order by 1)
           || ' end' || chr(13) || chr(10) ||
           'from dual'
    into vSQL
    from ( select '<'  as op from dual union all
           select '>'        from dual union all
           select '>='       from dual union all
           select '>='       from dual
         ) operators
    cross join (
                select level as num
                from dual
                connect by level <= 2
               )  n1
    cross join (
                select level -1 as num
                from dual
                connect by level <= 1 
               ) n2;
    --
    /* print the query */
    dbms_output.put_line(vSQL);
    /* run the dynamic query just built and get the result */
    execute immediate vSQL into vResult;
    /* print the result */
    dbms_output.put_line(vResult);
end;

When run, this gives:

select case 
when 1<0 then '1<0'
when 1>0 then '1>0'
when 1>=0 then '1>=0'
when 1>=0 then '1>=0'
when 2<0 then '2<0'
when 2>0 then '2>0'
when 2>=0 then '2>=0'
when 2>=0 then '2>=0' end
from dual
1>0

With variables, this:

declare
    vNumVar1    number;
    vNumVar2    number;
    vOpVar      varchar2(2);
    vSQL        varchar2(100);
    vResult     varchar2(100);
begin
    vNumVar1 := 1;
    vNumVar2 := 3;
    vOpVar   := '<='; 
    vSQL := 'select case when ' || vNumVar1 || vOpVar || vNumVar2 || ' then ''something'' end from dual';
    dbms_output.put_line(vSQL);
    execute immediate vSQL
    into vResult;
    dbms_output.put_line(vResult);
end;

gives: 

select case when 1<=3 then 'something' end from dual
something

Upvotes: 2

Related Questions