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

    Unanswered: Constructing dynamic where clause

    Hi guys,

    iam facing a problem in constructing dynamic where clause.
    here iam sending all the code. The basic is iam passing four values as input parameters from front end form, but i can send a value or null, so i want to check for null,
    CREATE OR REPLACE PROCEDURE SP_DATEREPORT
    (
    P_FROMDATE IN VARCHAR2,
    P_TODATE IN VARCHAR2,
    P_STATUS IN VARCHAR2,
    P_FILENAME IN VARCHAR2,
    CUR_DAILY_ACTIVE OUT REF_CURSOR

    )
    AS
    V_ERRORINFO STATUS_REASON.DISPLAYVALUE%TYPE;

    BEGIN
    OPEN CUR_DAILY_ACTIVE FOR
    SELECT DISTINCT A.SOURCEGROUP,
    A.FILERECEIVEDATE,
    A.INFILENAME,
    A.CREATEDDATE,
    A.FILERECEIVEDATE,
    A.DEPOSITAMOUNT,
    A.NOOFRECORDS,
    A.TOTALAMOUNT,
    (SELECT COUNT(*) FROM PAYMENT_DETAIL PD1 WHERE
    PD1.INFILENAME=B.INFILENAME AND
    PD1.STATUSCODE='GOOD' AND
    PD1.SOURCEGROUP = B.SOURCEGROUP AND
    PD1.PAYMENTDATE=B.PAYMENTDATE AND
    PD1.PAYMENTAMOUNT >= 0) AS GCOUNT,
    (SELECT SUM(PAYMENTAMOUNT) FROM PAYMENT_DETAIL PD2 WHERE
    PD2.INFILENAME=B.INFILENAME AND
    PD2.STATUSCODE='GOOD' AND
    PD2.SOURCEGROUP = B.SOURCEGROUP AND
    PD2.PAYMENTDATE=B.PAYMENTDATE AND
    PD2.PAYMENTAMOUNT >= 0) AS GAMOUNT,
    (SELECT COUNT(*) FROM PAYMENT_DETAIL PD3 WHERE
    PD3.INFILENAME=B.INFILENAME AND
    PD3.STATUSCODE='ERROR' AND
    PD3.SOURCEGROUP = B.SOURCEGROUP AND
    PD3.PAYMENTDATE=B.PAYMENTDATE AND
    PD3.PAYMENTAMOUNT >= 0) AS ECOUNT,
    (SELECT SUM(PAYMENTAMOUNT) FROM PAYMENT_DETAIL PD4 WHERE
    PD4.INFILENAME=B.INFILENAME AND
    PD4.STATUSCODE='ERROR' AND
    PD4.SOURCEGROUP = B.SOURCEGROUP AND
    PD4.PAYMENTDATE=B.PAYMENTDATE AND
    PD4.PAYMENTAMOUNT >= 0) AS EAMOUNT,
    (SELECT COUNT(*) FROM PAYMENT_DETAIL PD5 WHERE
    PD5.INFILENAME=B.INFILENAME AND
    PD5.STATUSCODE='MANUALLYCORRECTED' AND
    PD5.SOURCEGROUP = B.SOURCEGROUP AND
    PD5.PAYMENTDATE=B.PAYMENTDATE AND
    PD5.PAYMENTAMOUNT >= 0)AS CCOUNT,
    (SELECT SUM(PAYMENTAMOUNT) FROM PAYMENT_DETAIL PD6 WHERE
    PD6.INFILENAME=B.INFILENAME AND
    PD6.STATUSCODE='MANUALLYCORRECTED' AND
    PD6.SOURCEGROUP = B.SOURCEGROUP AND
    PD6.PAYMENTDATE=B.PAYMENTDATE AND
    PD6.PAYMENTAMOUNT >= 0)AS CAMOUNT,
    (SELECT COUNT(*) FROM PAYMENT_DETAIL PD7 WHERE
    PD7.INFILENAME=B.INFILENAME AND
    PD7.STATUSCODE='OTHERS' AND
    PD7.SOURCEGROUP = B.SOURCEGROUP AND
    PD7.PAYMENTDATE=B.PAYMENTDATE AND
    PD7.PAYMENTAMOUNT >= 0)AS OCOUNT,
    (SELECT SUM(PAYMENTAMOUNT) FROM PAYMENT_DETAIL PD8 WHERE
    PD8.INFILENAME=B.INFILENAME AND
    PD8.STATUSCODE='OTHERS' AND
    PD8.SOURCEGROUP = B.SOURCEGROUP AND
    PD8.PAYMENTDATE=B.PAYMENTDATE AND
    PD8.PAYMENTAMOUNT >= 0)AS OAMOUNT,E.OUTFILENAME,E.FILEDISPATCHDATE
    FROM IN_FILE A,PAYMENT_DETAIL B,OUT_FILE E
    WHERE A.INFILENAME=B.INFILENAME AND
    B.OUTFILENAME=E.OUTFILENAME AND
    A.INFILENAME=P_FILENAME ;

    END;

    /


    This is the procedure , here i want to check null or not for all the four input values and i have to construct a dynamic where clause.

    If anyone know how to do this pls let me know.
    Thank you

  2. #2
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    One solution can be a dynamic SQL and then execute this dynamic SQL using 'execute immediate'.

    Other solution could be
    replace
    A.INFILENAME=P_FILENAME ;
    by
    NVL(A.INFILENAME,'')=P_FILENAME ;
    Oracle can do wonders !

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You have to be careful with '' (assuming there isnt a space in there) as in Oracle unlike some other DBs it evaluates to null, so to test for null use a string which you can guarantee wont appear in your table. The other point is what does null mean to your user, does it indicate match any value or does it really mean match a null value in the table?

    if null should match null in your table then try
    NVL(A.INFILENAME,'***')=NVL(P_FILENAME,'***') ;

    if null matches any value then you need to use
    AND ((p_filename is not null and p.filename = a.infilename) or (p_filename is null))

    Alan

Posting Permissions

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