0 Replies Latest reply: May 19, 2014 6:11 AM by user2875559 RSS

    Passing multiple parameter to where condition

    user2875559

      Hi all,

       

      I want to pass multiple parameters to where condition.. it is as follows:

       

      i'll pass only TODAY date to M_DATE_TO variable.

       

      CURSOR C_COUNT(M_DATE_FROM  DATE, M_DATE_TO  DATE) IS

      SELECT COUNT (*)

      FROM table_name

      WHERE   TRUNC(table_name.join_date) BETWEEN M_DATE_FROM AND M_DATE_TO;

       

       

       

      begin

        SELECT TRUNC(LAST_DAY(ADD_MONTHS(:REP_VALUE_4,-1)))+1 INTO M_FIRST_DATE_MONTH FROM DUAL;

       

        SELECT TRUNC(:REP_VALUE_4, 'YEAR') INTO M_FIRST_DATE_YEAR FROM DUAL;

       

        SELECT TRUNC(ADD_MONTHS(:REP_VALUE_4,-12)) M_SAME_DATE_PYR FROM DUAL;

       

        SELECT TRUNC(LAST_DAY(ADD_MONTHS(:REP_VALUE_4,-13)))+1 INTO M_FIRST_DATE_MONTH_PYR FROM DUAL;

       

        SELECT ADD_MONTHS(TRUNC(:REP_VALUE_4,'YEAR'), -12) INTO M_FIRST_DAY_PYR FROM DUAL;

       

        M_DATE_FROM:=M_FIRST_DATE_MONTH;

        M_DATE_TO := TODAY;

        IF C_COUNT%ISOPEN THEN

        CLOSE C_COUNT;

        END IF;

        OPEN C_COUNT (M_DATE_FROM,M_DATE_TO);

        FETCH C_COUNT INTO M_COUNT_MTD;

        CLOSE C_COUNT;

        :cp_count_mtd:=m_count_mtd;

       

      M_DATE_FROM:=M_FIRST_DATE_YEAR;

      M_DATE_TO:= TODAY;

        IF C_COUNT%ISOPEN THEN

        CLOSE C_COUNT;

        END IF;

        OPEN C_COUNT (M_DATE_FROM,M_DATE_TO);

        FETCH C_COUNT INTO M_COUNT_YTD;

        CLOSE C_COUNT;

        :cp_count_ytd:=m_count_ytd;

       

      M_DATE_FROM:=M_FIRST_DATE_MONTH_PYR;

      M_DATE_TO:= M_FIRST_DAY_PYR;

        IF C_COUNT%ISOPEN THEN

        CLOSE C_COUNT;

        END IF;

      OPEN C_COUNT (M_DATE_FROM,M_DATE_TO);

        FETCH C_COUNT INTO M_COUNT_MTD_PYR;

        CLOSE C_COUNT;

        :cp_count_ytd_pyr :=m_count_ytd;

       

      Is there any easy way or other method, Please help me...Thanks in advance.

       

      Regi