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

    Unanswered: Getting problem with Dynamic where clause

    Hi all,

    Iam getting an error with the procedure, i could not understand what is the problem, the code is here

    CREATE OR REPLACE PACKAGE BODY DUMMY AS
    PROCEDURE SP_DUMMYACTIVITY
    (
    P_FROMDATE IN VARCHAR2,
    P_TODATE IN VARCHAR2,
    P_STATUS IN VARCHAR2,
    P_FILENAME IN VARCHAR2,
    CUR_DAILY_ACTIVE OUT REF_CURSOR

    )
    AS
    FILERECEIVEDATE IN_FILE.FILERECEIVEDATE%TYPE;
    L_MATCH_RECORDS NUMBER;
    L_SQL_STMT VARCHAR2(500);
    L_WHERECLAUSE VARCHAR2(500);
    L_ANDCLAUSE VARCHAR2(10) :='AND';
    L_STRING VARCHAR2(500):= 'A.INFILENAME=B.INFILENAME AND B.OUTFILENAME=E.OUTFILENAME' ;

    BEGIN

    IF (P_FROMDATE IS NOT NULL) THEN
    L_WHERECLAUSE := 'FILERECEIVEDATE >= P_FROMDATE'|| L_ANDCLAUSE || L_STRING ;
    END IF;

    IF (P_TODATE IS NOT NULL) THEN
    L_WHERECLAUSE := L_WHERECLAUSE || L_ANDCLAUSE || 'FILERECEIVEDATE <= P_TODATE';
    END IF;

    /*IF (P_FILENAME IS NOT NULL) THEN
    L_WHERECLAUSE := L_WHERECLAUSE||L_ANDCLAUSE || 'P_TODATE < P_FROMDATE';
    END IF;*/

    /*IF(P_STATUS IS NOT NULL) THEN
    L_WHERECLAUSE := L_WHERECLAUSE;
    END IF;

    IF(P_FROMDATE IS NULL AND P_TODATE IS NULL AND P_FILENAME IS NOT NULL AND P_STATUS IS NULL) THEN
    L_WHERECLAUSE := L_STRING;
    END IF;

    IF(P_FROMDATE IS NULL AND P_TODATE IS NULL AND P_FILENAME IS NOT NULL AND P_STATUS IS NOT NULL) THEN
    L_WHERECLAUSE := L_STRING;
    END IF;

    IF(P_FROMDATE IS NULL AND P_TODATE IS NULL AND P_FILENAME IS NULL AND P_STATUS IS NOT NULL) THEN
    L_WHERECLAUSE := L_STRING;
    END IF;

    IF (P_FROMDATE IS NULL AND P_TODATE IS NULL AND P_FILENAME IS NULL AND P_STATUS IS NULL) THEN
    L_WHERECLAUSE := L_STRING;
    END IF;*/



    /*Fetch the records.*/

    OPEN CUR_DAILY_ACTIVE FOR
    'SELECT distinct A.INFILENAME,A.SOURCEGROUP,
    TO_CHAR(A.FILERECEIVEDATE,MM/DD/YYYY),
    TO_CHAR(A.CREATEDDATE,MM/DD/YYYY),
    TO_CHAR(A.FILERECEIVEDATE,MM/DD/YYYY),
    A.DEPOSITAMOUNT,
    A.NOOFRECORDS,
    A.TOTALAMOUNT,
    (SELECT COUNT(*) FROM PAYMENT_DETAIL PD1 WHERE
    PD1.INFILENAME=A.INFILENAME AND
    PD1.STATUSCODE=GOOD) AS GCOUNT,
    (SELECT SUM(PAYMENTAMOUNT) FROM PAYMENT_DETAIL PD2 WHERE
    PD2.INFILENAME=A.INFILENAME AND
    PD2.STATUSCODE=GOOD) AS GAMOUNT,
    (SELECT COUNT(*) FROM PAYMENT_DETAIL PD3 WHERE
    PD3.INFILENAME=A.INFILENAME AND
    PD3.STATUSCODE=ERROR) AS ECOUNT ,
    (SELECT SUM(PAYMENTAMOUNT) FROM PAYMENT_DETAIL PD4 WHERE
    PD4.INFILENAME=A.INFILENAME AND
    PD4.STATUSCODE=ERROR) AS EAMOUNT,
    (SELECT COUNT(*) FROM PAYMENT_DETAIL PD5 WHERE
    PD5.INFILENAME=A.INFILENAME AND
    PD5.STATUSCODE=MANUALLYCORRECTED)AS CCOUNT,
    (SELECT SUM(PAYMENTAMOUNT) FROM PAYMENT_DETAIL PD6 WHERE
    PD6.INFILENAME=A.INFILENAME AND
    PD6.STATUSCODE=MANUALLYCORRECTED)AS CAMOUNT,
    (SELECT COUNT(*) FROM PAYMENT_DETAIL PD7 WHERE
    PD7.INFILENAME=A.INFILENAME AND
    PD7.STATUSCODE=OTHERS)AS OCOUNT ,
    (SELECT SUM(PAYMENTAMOUNT) FROM PAYMENT_DETAIL PD8 WHERE
    PD8.INFILENAME=B.INFILENAME AND
    PD8.STATUSCODE=OTHERS)AS OAMOUNT,
    E.OUTFILENAME,
    E.FILEDISPATCHDATE

    FROM IN_FILE A,PAYMENT_DETAIL B,OUT_FILE E
    WHERE' || L_WHERECLAUSE;

    END;
    END DUMMY;
    /

    and iam getting SQL command is not properly ended at line number something, and the line no is open cursor statement. then any one can help me in this one, where the syntax is right or not.

    Thank you.

  2. #2
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    1. Give description of table PAYMENT_DETAIL
    2. PD1.STATUSCODE=GOOD --> This is correct if GOOD is a column in this table. Else, enclose in single quotes 'GOOD'
    3. Instead of firing multiple queries on this table for each valueof status code, try using a decode. It will make yoru query easy.

    eg.
    sum(DECODE(statuscode, 'MANUALLYCORRECTED', PAYMENTAMOUNT, 0)) as CAAMOUNT,
    count(DECODE(status_code, 'OTHERS',1,0)) as OCOUNT,
    sum(DECODE(statuscode, 'GOOD', PAYMENTAMOUNT, 0)) as OAMOUNT
    Oracle can do wonders !

  3. #3
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    On usage of dynamic ref cursor as out parameter of a proc refer:
    http://otn.oracle.com/oramag/code/tips2003/042003.html
    Oracle can do wonders !

  4. #4
    Join Date
    Jan 2004
    Posts
    492

    Re: Getting problem with Dynamic where clause

    Originally posted by kirankumarb

    and iam getting SQL command is not properly ended at line number something, and the line no is open cursor statement. then any one can help me in this one, where the syntax is right or not.

    Thank you.

    If you are going to open a cursor as a string for dynamic purposes, you need to concatenate any strings wrapping over more than one line. You also need to include 2 single quotes ('') inside the large string to simulate a single quote (in the case of a regular string). A good trick I also do it to leave one blank space before the ending quote on a line. If you dont, you will concatenate words directly together instead of leaving the space you need.

    For instance:

    Code:
    open c_ref for
    'Select ename, '  ||
    'empno, sal ' ||
    'comm ' ||
    'from emp where '||
    'job = ''PRESIDENT''';
    Notice how you are making several string b/c they are on separate lines. And b/c PRESIDENT is a string within a string, you insert 2 single quotes on each side of it.

    You can then include your dynamic where statement at the end too by just concatenating it in the same manner.

    Hope this helps

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

    Re: Getting problem with Dynamic where clause

    Look at this part:
    PHP Code:
    IF (P_TODATE IS NOT NULLTHEN
      L_WHERECLAUSE 
    := L_WHERECLAUSE || L_ANDCLAUSE || 'FILERECEIVEDATE <= P_TODATE';
    END IF; 
    This appends ' AND FILERECEIVEDATE <= P_TODATE' to the SQL. When the SQL is run it will expect to find a column called P_TODATE, when there is none.

    You should instead do something like this:
    PHP Code:
    IF (P_TODATE IS NOT NULLTHEN
      L_WHERECLAUSE 
    := L_WHERECLAUSE || L_ANDCLAUSE || 'FILERECEIVEDATE <= :TODATE';
    ELSE
      
    L_WHERECLAUSE := L_WHERECLAUSE || L_ANDCLAUSE || ':TODATE IS NULL';
    END IF; 
    Then bind the value of P_TODATE to the bind variable :TODATE when you execute the SQL:

    OPEN CUR_DAILY_ACTIVE FOR
    'SELECT distinct A.INFILENAME,A.SOURCEGROUP,
    ...
    WHERE' || L_WHERECLAUSE
    USING P_TODATE, ...;

    (The additional ELSE clause I added is to ensure that the query always has a :TODATE bind variable to bind P_TODATE to, even if it is redundant).

  6. #6
    Join Date
    Dec 2003
    Posts
    42

    Re: Getting problem with Dynamic where clause

    Originally posted by andrewst
    Look at this part:
    PHP Code:
    IF (P_TODATE IS NOT NULLTHEN
      L_WHERECLAUSE 
    := L_WHERECLAUSE || L_ANDCLAUSE || 'FILERECEIVEDATE <= P_TODATE';
    END IF; 
    This appends ' AND FILERECEIVEDATE <= P_TODATE' to the SQL. When the SQL is run it will expect to find a column called P_TODATE, when there is none.

    You should instead do something like this:
    PHP Code:
    IF (P_TODATE IS NOT NULLTHEN
      L_WHERECLAUSE 
    := L_WHERECLAUSE || L_ANDCLAUSE || 'FILERECEIVEDATE <= :TODATE';
    ELSE
      
    L_WHERECLAUSE := L_WHERECLAUSE || L_ANDCLAUSE || ':TODATE IS NULL';
    END IF; 
    Then bind the value of P_TODATE to the bind variable :TODATE when you execute the SQL:

    OPEN CUR_DAILY_ACTIVE FOR
    'SELECT distinct A.INFILENAME,A.SOURCEGROUP,
    ...
    WHERE' || L_WHERECLAUSE
    USING P_TODATE, ...;

    (The additional ELSE clause I added is to ensure that the query always has a :TODATE bind variable to bind P_TODATE to, even if it is redundant).

    hI,

    i got your point, but how can i bind the variable, iam giving some variable with that particular column type, but still iam getting the same error, can you tell me how can we declare the variables pls give some examples to bind.

    thank you

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

    Re: Getting problem with Dynamic where clause

    A simple example:
    PHP Code:
    create or replace procedure show_emps
      
    p_job in varchar2
      
    p_deptno in number
      
    )
    is
      v_sql varchar2
    (200) := 'select * from emp where 1=1';
      
    type rc_type is ref cursor;
      
    rc rc_type;
      
    v_emp emp%rowtype;
    begin
      
    if p_job is not null then
        v_sql 
    := v_sql || ' and job = :job';
      else
        
    v_sql := v_sql || ' and :job is null';
      
    end if;
      if 
    p_deptno is not null then
        v_sql 
    := v_sql || ' and deptno = :deptno';
      else
        
    v_sql := v_sql || ' and :deptno is null';
      
    end if;
      
    dbms_output.put_line'SQL = '||v_sql );
      
    open rc for v_sql using p_jobp_deptno;
      
    loop
        fetch rc into v_emp
    ;
        exit 
    when rc%notfound;
        
    dbms_output.put_linev_emp.ename || ' ' || v_emp.job || ' ' || v_emp.deptno );
      
    end loop;
      
    close rc;
    end;

    Demo:
    PHP Code:
    SQLset serverout on 

    SQL
    exec show_empsNULL10 )
    SQL select from emp where 1=and :job is null and deptno = :deptno
    CLARK MANAGER 10
    KING PRESIDENT 10
    MILLER CLERK 10

    PL
    /SQL procedure successfully completed.

    SQLexec show_emps'MANAGER'NULL )
    SQL select from emp where 1=and job = :job and :deptno is null
    JONES MANAGER 20
    BLAKE MANAGER 30
    CLARK MANAGER 10

    PL
    /SQL procedure successfully completed.

    SQLexec show_emps'MANAGER'10 )
    SQL select from emp where 1=and job = :job and deptno = :deptno
    CLARK MANAGER 10

    PL
    /SQL procedure successfully completed.

    SQLexec show_empsNULLNULL )
    SQL select from emp where 1=and :job is null and :deptno is null
    SMITH CLERK 20
    ALLEN SALESMAN 30
    WARD SALESMAN 30
    JONES MANAGER 20
    MARTIN SALESMAN 30
    BLAKE MANAGER 30
    CLARK MANAGER 10
    SCOTT ANALYST 20
    KING PRESIDENT 10
    TURNER SALESMAN 30
    ADAMS CLERK 20
    JAMES CLERK 30
    FORD ANALYST 20
    MILLER CLERK 10

    PL
    /SQL procedure successfully completed

  8. #8
    Join Date
    Dec 2003
    Posts
    42

    Re: Getting problem with Dynamic where clause

    Hi

    iam trying with your code, but the input parameters coming to this procedure is not database fields, for example the two are values are checking whether it is greater or less thjan the receive date, so how can i give this date values in my dynamic where clause, here iam sending all the code pls give the solution. iam including the where clause also.

    CREATE OR REPLACE PACKAGE BODY DUMMY AS

    PROCEDURE SP_DUMMYACTIVITY
    (
    P_FROMDATE IN VARCHAR2,
    P_TODATE IN VARCHAR2,
    P_STATUS IN VARCHAR2,
    P_FILENAME IN VARCHAR2,
    CUR_DAILY_ACTIVE OUT REF_CURSOR

    )
    AS

    FILERECEIVEDATE IN_FILE.FILERECEIVEDATE%TYPE;
    V_FROMDATE VARCHAR2(30) := P_FROMDATE;
    V_TODATE VARCHAR2(30) := P_TODATE;
    V_FILENAME VARCHAR2(100) := P_FILENAME;
    V_STATUS VARCHAR2(30) := P_STATUS;
    L_WHERECLAUSE VARCHAR2(1500);
    L_STRING VARCHAR2(500);
    L_ANDCLAUSE VARCHAR2(10);
    L_FROMDATEVALUE VARCHAR2(500);
    L_TODATEVALUE VARCHAR2(300);
    L_FILENAMEVALUE VARCHAR2(300);
    L_STATUSVALUE VARCHAR2(100);

    BEGIN

    L_STRING := ' A.INFILENAME=B.INFILENAME' ;

    L_ANDCLAUSE :=' AND ';


    IF (P_FROMDATE IS NOT NULL AND P_TODATE IS NULL AND P_FILENAME IS NULL AND P_STATUS IS NOT NULL) THEN
    L_WHERECLAUSE := L_STRING || L_ANDCLAUSE || ' A.FILERECEIVEDATE >= V_FROMDATE ' ;
    END IF;

    IF (P_FROMDATE IS NOT NULL AND P_TODATE IS NOT NULL AND P_FILENAME IS NOT NULL AND P_STATUS IS NOT NULL) THEN
    L_WHERECLAUSE := L_STRING ||L_ANDCLAUSE || ' A.FILERECEIVEDATE BETWEEN V_FROMDATE AND V_TODATE ' || L_ANDCLAUSE ||' A.INFILENAME = V_FILENAME ' || L_ANDCLAUSE || ' A.STATUSCODE = V_STATUS ';
    END IF;

    IF (P_FROMDATE IS NULL AND P_TODATE IS NULL AND P_FILENAME IS NOT NULL AND P_STATUS IS NULL) THEN
    L_WHERECLAUSE := L_STRING || L_ANDCLAUSE || ' A.INFILENAME = V_FILENAME ';
    END IF;

    IF (P_FROMDATE IS NULL AND P_TODATE IS NULL AND P_FILENAME IS NOT NULL AND P_STATUS IS NOT NULL) THEN
    L_WHERECLAUSE := L_STRING || L_ANDCLAUSE || ' A.INFILENAME = V_FILENAME ' || L_ANDCLAUSE || ' A.STATUSCODE = V_STATUS ';
    END IF;

    IF (P_FROMDATE IS NULL AND P_TODATE IS NULL AND P_FILENAME IS NULL AND P_STATUS IS NOT NULL) THEN
    L_WHERECLAUSE := L_STRING || L_ANDCLAUSE || ' A.STATUSCODE = V_STATUS ';
    END IF;


    /*Fetch the records.*/

    OPEN CUR_DAILY_ACTIVE FOR

    'SELECT DISTINCT A.INFILENAME,A.SOURCEGROUP,
    TO_CHAR(A.FILERECEIVEDATE,''MM/DD/YYYY''),
    TO_CHAR(A.CREATEDDATE,''MM/DD/YYYY''),
    TO_CHAR(A.FILERECEIVEDATE,''MM/DD/YYYY''),
    A.DEPOSITAMOUNT,
    A.NOOFRECORDS,
    A.TOTALAMOUNT,
    (SELECT COUNT(*) FROM PAYMENT_DETAIL PD1 WHERE
    PD1.INFILENAME=A.INFILENAME AND
    PD1.STATUSCODE=''GOOD'') AS GCOUNT,
    (SELECT SUM(PAYMENTAMOUNT) FROM PAYMENT_DETAIL PD2 WHERE
    PD2.INFILENAME=A.INFILENAME AND
    PD2.STATUSCODE=''GOOD'') AS GAMOUNT,
    (SELECT COUNT(*) FROM PAYMENT_DETAIL PD3 WHERE
    PD3.INFILENAME=A.INFILENAME AND
    PD3.STATUSCODE=''ERROR'') AS ECOUNT ,
    (SELECT SUM(PAYMENTAMOUNT) FROM PAYMENT_DETAIL PD4 WHERE
    PD4.INFILENAME=A.INFILENAME AND
    PD4.STATUSCODE=''ERROR'') AS EAMOUNT,
    (SELECT COUNT(*) FROM PAYMENT_DETAIL PD5 WHERE
    PD5.INFILENAME=A.INFILENAME AND
    PD5.STATUSCODE=''MANUALLYCORRECTED'')AS CCOUNT,
    (SELECT SUM(PAYMENTAMOUNT) FROM PAYMENT_DETAIL PD6 WHERE
    PD6.INFILENAME=A.INFILENAME AND
    PD6.STATUSCODE=''MANUALLYCORRECTED'')AS CAMOUNT,
    (SELECT COUNT(*) FROM PAYMENT_DETAIL PD7 WHERE
    PD7.INFILENAME=A.INFILENAME AND
    PD7.STATUSCODE=''OTHERS'')AS OCOUNT ,
    (SELECT SUM(PAYMENTAMOUNT) FROM PAYMENT_DETAIL PD8 WHERE
    PD8.INFILENAME=B.INFILENAME AND
    PD8.STATUSCODE=''OTHERS'')AS OAMOUNT


    FROM IN_FILE A,PAYMENT_DETAIL B
    WHERE' || L_WHERECLAUSE ;






    END;
    END DUMMY;
    /



    Thank you...

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

    Re: Getting problem with Dynamic where clause

    The only real difference between my previous example and your requirement is that you want to use ">=" instead of "="? You should be able to extrapolate easily from that example, but anyway I have extended my example to pass in an additional DATE parameter called v_from_date, which is used to test emp.hiredate using the ">=" operator.

    I'm sure you have enough to get you going now. I'm afraid I don't have the time to correct and test your code.

    PHP Code:
    create or replace procedure show_emps
      
    p_job in varchar2
      
    p_deptno in number
      
    p_from_date in date
      
    )
    is
      v_sql varchar2
    (200) := 'select * from emp where 1=1';
      
    type rc_type is ref cursor;
      
    rc rc_type;
      
    v_emp emp%rowtype;
    begin
      
    if p_job is not null then
        v_sql 
    := v_sql || ' and job = :job';
      else
        
    v_sql := v_sql || ' and :job is null';
      
    end if;
      if 
    p_deptno is not null then
        v_sql 
    := v_sql || ' and deptno = :deptno';
      else
        
    v_sql := v_sql || ' and :deptno is null';
      
    end if;
      if 
    p_from_date is not null then
        v_sql 
    := v_sql || ' and hiredate >= :fromdate';
      else
        
    v_sql := v_sql || ' and :fromdate is null';
      
    end if;
      
    dbms_output.put_line'SQL = '||v_sql );
      
    open rc for v_sql using p_jobp_deptnop_from_date;
      
    loop
        fetch rc into v_emp
    ;
        exit 
    when rc%notfound;
        
    dbms_output.put_linev_emp.ename || ' ' || v_emp.job || ' ' || v_emp.deptno );
      
    end loop;
      
    close rc;
    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
  •