Dynamic Query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Dynamic Query

  1. #1
    Join Date
    Sep 2002
    Posts
    1
    SELECT A.FIRMNBR
    FROM TA_ALARM_D A, TA_RATE_D B
    WHERE B.PRODUCTID = A.PRODUCTID
    AND B.TRANSTYPE = A.TRANSTYPE
    AND B.RATE || ( SELECT OPERATOR FROM TA_ALARM_D C
    WHERE C.PRODUCTID = A.PRODUCTID
    AND C.TRANSTYPE = A.TRANSTYPE ) || A.RATE

    Hi,

    In the above query, in the last condition of 'where clause', I want to pass some relational operators
    (like '=','<','>' etc.) dynamically from a table column and then parse the main query to get the result records
    from the outer main query.
    How could i achieve this.

  2. #2
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282


    use 'execute immediate .... using ...' clause;


    F.

  3. #3
    Join Date
    Sep 2002
    Posts
    42
    Please be more specific

    The query you have given me i am not able to understand

    Does that work .There is no relational parameter


    In GOD I find everything

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Lightbulb

    Use Native Dynamic SQL (NDS). See http://otn.oracle.com/docs/products/...9dyn.htm#26622 for details.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Try this hope this help U
    declare
    lv_input_str varchar2(100) := '>';
    lv_input_sal number := 0;
    lv_output_sal number;
    begin
    execute immediate 'select count(*) from emp where sal ' || lv_input_str || lv_input_sal INTO lv_output_sal;
    --dbms_output.put_line('select count(*) from emp where sal ' || lv_input_str || lv_input_sal);
    dbms_output.put_line('lv_output_sal >>>' || lv_output_sal);
    end;

    Cheers!
    Cheers!
    OraKid.

Posting Permissions

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


Click Here to Expand Forum to Full Width