Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Posts
    5

    Unanswered: Need help in designing runtime query in oracle

    Hi All,
    Below is my stored procedure.I need a help in designing runtime query.Some of my input parameter are optional like product_no,
    productgroup,mproductgroup, cust_no,sales_channel_code,branded based on value coming from application to this parameter it will process on stored procedure.I declare a var_a where i am taking all my where condition.I have a problem of how to assigning parameter in declare variable which is concating with where clause.
    Any help is appreciate.
    Procedure SA_STRATEGIC_BASIC
    (
    terminal_no in varchar2,
    stockholder in varchar2,
    begin_date in date,
    end_date in date,
    product_no in varchar2,
    productgroup in varchar2,
    mproductgroup in varchar2,
    cust_no in varchar2,
    sales_channel_code in varchar2,
    branded in varchar2
    )
    IS
    declare var_a varchar2(200) := 'A.SHIP_TO_CUSTOMER_KEY = B.CUSTOMER_KEY AND A.MATERIAL_KEY = C.MATERIAL_KEY
    AND A.PLANT_KEY = D.PLANT_KEY and d.plant_no = terminal_no and b.company_code = stockholder
    AND (A.PERIOD_DATE >= to_date (begin_date,'DD-MM-YYYY'))
    AND (A.PERIOD_DATE <= to_date (end_date,'DD-MM-YYYY'))'
    begin
    if product_no <> null then
    var_a = var_a || ' and C.MATERIAL_NO = ' || product_no;
    end if

    --> It is the right way of declaring product_no in runtine query which is also concating with a variable var_a if not can you please suggest me how to do.

    if productgroup<> null then
    var_a = var_a || ' and C.MATERIAL_NO In (select alliance_prod_code from to_product_group_xref
    where prod_group_code = ' || productgroup || ')';
    end if

    if mproductgroup <> null then
    var_a = var_a || ' and C.MATERIAL_NO in(SELECT distinct MATERIAL_NO FROM OPS$MATRIX.MATERIAL
    where material_group_code = ' || mproductgroup || ')';
    end if;

    if cust_no <> null then
    var_a = var_a || ' AND B.sold_to_customer_NO = ' || cust_no;
    end if;
    if branded_indicator <> null then
    var_a = var_a || ' AND b.sales_channel_code IN (' || branded_indicator || ')';
    end if;
    var_a = var_a || 'group by sold_to_customer_no,customer_name,d.plant_no';
    select sold_to_customer_no ,
    customer_name ,
    sum(GROSS_MATERIAL_QTY) ,
    sum(net_material_qty)
    FROM MATRIX_SALES_Daily_SUMM_P A,
    MATRIX_CUSTOMER B,
    MATRIX_MATERIAL C,
    MATRIX_PLANT D
    WHERE var_a
    group by sold_to_customer_no,customer_name;

    end;

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Need help in designing runtime query in oracle

    1) You cannot say "if var <> null" - that will NEVER be true. You must say "if var IS null"

    2) You are trying to perform dynamic SQL, but are mixing dynamic and static together. For dynamic SQL you must put the whole SELECT statement into a VARCHAR2 variable and then use EXECUTE IMMEDIATE or the DBMS_SQL package.

    3) You should be using bind variables for values, not concatenate them into your statement, otherwise your database will perform poorly.

    Simple example:

    DECLARE
    v_sql VARCHAR2(10000);
    v_id NUMBER := 123;
    v_name VARCHAR2(30);
    BEGIN
    v_sql := 'SELECT name FROM emp WHERE id = :id';
    EXECUTE IMMEDIATE v_sql
    INTO v_name
    USING v_id;
    END;

Posting Permissions

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