Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    42

    Unanswered: dynamic where clause with if condition

    Hi all,

    actually iam trying to construct a dynamic where clause and i want to append it to my select statement.

    for example,

    create or replace procedure xxxx
    (
    abc in varchar2,
    dfbdf in varchar2,
    ddd in varchar2,
    ddd in varchar2)
    as
    l_whereclause varchar2(500);
    begin
    if (abc is not null) then
    l_whereclause := -------
    dynamic where clause;
    end if;
    if (dfbdf is not null) then
    dynamic where clause;
    end if;
    ..
    ..
    ..
    open cursor for
    select * from table where || (dynamic where clause);
    end;

    This is iam trying rightnow, but i dont know exactly how to write the dynamic clause, can any one suggest me. i want to check the input values using if condition and then construct the where clause

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    if (abc is not null) then
    l_whereclause := l_whereclause||' appended text';
    end if;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Create dynamic SQL by contacting string to it.
    User DBMS_SQL or "execute immediate"
    Oracle can do wonders !

  4. #4
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    You can also do this with a REF CURSOR:

    Code:
       TYPE curvar_type IS REF CURSOR;
       curvar                        curvar_type;
    
        my_query_v    VARCHAR2(1000) := 'select * from something';
        where_v          VARCHAR2(100);
        BEGIN
           ...
           IF (abc is null)
               where_v := 'xyz=null';
           ELSE
               where_v := 'xyz='''||abc'' ';
           END IF;
    
            my_query_v := my_query_v || ' WHERE ' || where_v;
    
            OPEN curvar FOR my_query_v;
             LOOP
                FETCH curvar INTO var1, var2, var3;
                EXIT WHEN curvar%NOTFOUND;
                 ...
             END LOOP;
    ...
    NOTE: in the statement "my_query_v := my_query_v ..." there is a SPACE before and after the WHERE.... this is because you are concatenating three strings together to form the statement and it should be a valid sql statement. If you experience trouble during execution, print the string before your OPEN statement to make sure the statement is valid...

    JoeB

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    EX OF DBMS_SQL

    DECLARE
    cursor_n1 int;
    c1 int;
    dummy int := 2;
    stmt1 long;
    begin

    ....
    ....
    ....
    stmt1 := 'SELECT mfg_part_id, substr(stock_um,1,3), qty_available_iss, id
    FROM part@'||v_database(x)||'
    WHERE Commodity_Code in ('||''''||'RAA'||''''||','||''''||'RAC'||''''||')
    AND UPPER(Product_Code) in ('||''''||'STOCK'||''''||','
    ||''''||'CORE'||''''||')';

    cursor_n1 := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_n1,stmt1,dummy);

    dbms_sql.define_column_char(cursor_n1,1,c_mfg_part _id,30);
    dbms_sql.define_column_char(cursor_n1,2,c_stock_uo m,15);
    dbms_sql.define_column(cursor_n1,3,c_qty_available _iss);
    dbms_sql.define_column_char(cursor_n1,4,c_id,30);

    c1 := dbms_sql.execute(cursor_n1);

    WHILE DBMS_SQL.FETCH_ROWS(cursor_n1)>0
    LOOP
    dbms_sql.column_value_char(cursor_n1,1,c_mfg_part_ id);
    dbms_sql.column_value_char(cursor_n1,2,c_stock_uom );
    dbms_sql.column_value(cursor_n1,3,c_qty_available_ iss);
    dbms_sql.column_value_char(cursor_n1,4,c_id);


    hth
    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •