CODEWITHSUNDEEP
sqloracle-databaseplsql
user2924127
user2924127

Reputation: 6252

Conditional Where Clause Oracle SQL based on procedure input varaible

I have a plsql procedure which take an input variable. This variable (my_plsql_var) I need to decide the where clause of my select statement. If my variable is A then I need to query a specific where clause, if it is B it uses that specific where clause and same goes for C. I have tried some queries but they do not work. The closest I have gotten is this, but there seems to be syntax errors and am even unsure if the query will produce what I need.

SELECT ID, 
    CASE(CAST WHEN my_col1 in ('A') and my_col2 = 'A' then 'A GROUP'
                             WHEN my_col1 in ('B') and my_col2 = 'B' then 'B GROUP'
                             WHEN my_col1 in ('C') and my_col2 = 'C' then 'C GROUP'
                             else null
        end as varachar2)) as my_awesome_col
        FROM
        my_table
        WHERE
        id= 100 and
        name = 'Smith' and 
        CASE (WHEN my_plsql_var = 'A' then my_col1 in ('A') and my_col2 = 'A'
             WHEN my_plsql_var = 'B' then my_col1 in ('B') and my_col2 = 'B' and my_special_col = 'B'
             WHEN my_plsql_var = 'C' then my_col1 in ('C') and my_col2 = 'C'
        end as varachar2)

Upvotes: 0

Views: 2520

Answers (4)

Avrajit Roy
Avrajit Roy

Reputation: 3303

        Hello you need to build the SELECT Clause dynamically based upon your input. Below is the example for this.


        CREATE OR REPLACE PROCEDURE TEST1_DYN(
        p_in IN VARCHAR2,
        p_ref OUT sys_refcursor )
    AS
      lv_select LONG;
    BEGIN
      lv_select:='SELECT ID,     
                  (CASE WHEN my_col1 in ('''||p_in||''')'|| 'and my_col2 = '''||p_in||''''||' then '||''''||p_in||' GROUP'''||
                  ' else null        
                  end)  my_awesome_col        
                  FROM        
                  my_table        
                  WHERE        
                  id= 100 and        
                  name = ''Smith'' and         
                  my_plsql_var = '||''''||p_in||''''||' then my_col1 in ('||''''||p_in||''''||') and my_col2 = '||''''||p_in||'''             
                  ';
      dbms_output.put_line(lv_select);
OPEN p_ref for lv_select;
    END;

Upvotes: 0

Bacon Bits
Bacon Bits

Reputation: 32180

Can this not just be simplified to this?

SELECT ID,
    my_plsql_var || ' GROUP' AS Group
FROM my_table
WHERE ID = 100
    AND NAME = 'Smith'
    AND (
           (my_plsql_var = 'A' AND my_col1 IN ('A') AND my_col2 = 'A')
        OR (my_plsql_var = 'B' AND my_col1 IN ('B') AND my_col2 = 'B' AND my_special_col = 'B')
        OR (my_plsql_var = 'C' AND my_col1 IN ('C') AND my_col2 = 'C')
    );

Upvotes: 1

knuckles
knuckles

Reputation: 101

You need to evaluate your inputs and build dynamically....

Not certain what you want the procedure to do but this one just opens a ref cursor presumably you would do something with it.

Hope this helps.

    create or replace procedure my_plsql_procedure
    (
        my_plsql_var in varchar2
    )
    is
        dataset sys_refcursor;
        strSql CLOB;
        strPred VARCHAR2(500);
        bAddOtherPred boolean := my_plsql_var = 'B';
    begin
        if bAddOtherPred then
            strPred :=  q'~ and my special_col = 'B' ~';
        else
            strPred := null;
        end if;

        strSql := q'~ 
            select id,
                   CASE when my_col1 = myCol2 and my_col1 = 'A' THEN 'A GROUP'
                        when my_col1 = myCol2 and my_col1 = 'B' THEN 'B GROUP'
                        when my_col1 = myCol2 and my_col1 = 'C' THEN 'C GROUP'
                        else null end as my_awesome_col
            from my_table
            where my_col1 = my_col2
            and my_col1 = :my_plsql_var 
            and id = 100
            and name = 'Smith' ~' || strPred;

        open dataset 
        for strSql
        using my_plsql_var;

    end;

Upvotes: 0

Shaun Peterson
Shaun Peterson

Reputation: 1790

Your best bet for this is to use dynamic SQL...

Build up your select statement in a string, then use execute immediate to run the query as below. The code below is untested so might have some syntax errors but should give you an idea of how to do it. Otherwise Google dynamic SQL.

my_sql_string := 'SELECT ID, 
                  CASE(CAST WHEN my_col1 in (''A'') and my_col2 = ''A'' then ''A GROUP'' 
                       WHEN my_col1 in (''B'') and my_col2 = ''B'' then ''B GROUP''
                       WHEN my_col1 in (''C'') and my_col2 = ''C'' then ''C GROUP''
                       else null
        end as varachar2)) as my_awesome_col
        FROM
        my_table
        WHERE
        id= 100 and
        name = ''Smith'' and ';

 if my_plsql_var = 'A' then
  my_sql_string := my_sql_string || 'my_col1 in (''A'') and my_col2 = ''A''';
 else if my_plsql_var = 'B' then 
  my_sql_string := my_sql_string || 'my_col1 in (''B'') and my_col2 = ''B''';
 else if my_plsql_var = 'C' then
  my_sql_string := my_sql_string || my_col1 in (''C'') and my_col2 = ''C''';
 end if;
v_output := execute immediate my_sql_string;

Upvotes: 0

Related Questions