6 Replies Latest reply: Dec 29, 2012 10:39 AM by rp0428 RSS

    needed help in table function

    980493
      Hi experts,
            I've created a type as 
      TYPE TEST_DWH_PROD AS OBJECT
                     (WO_RELEASE_DATE DATE,
                     SITE_CODE__MFG CHAR(5),
                     SITE_CODE__PARENT CHAR(5),
                     ORDER_TYPE CHAR(3),
                     MFG_TYPE VARCHAR2(10),
                     BOM_SUB_TYPE VARCHAR2(20),
                     ITEM_CODE CHAR(10),
                     STD_YIELD_PERC NUMBER(14,3),
                     WORK_ORDER_STATUS CHAR(1),                
                     PRODUCTION_QTY NUMBER(14,3),
                     RCP_TOL_PERC NUMBER(14,3),
                     ACTUAL_YIELD NUMBER(14,3),
                     HIGH_YIELD_VALUE NUMBER(14,3),
                     LOW_YIELD_VALUE NUMBER(14,3),
                     YIELD_GAIN_LOSS NUMBER(14,3),          
                     ACTUAL_PROD_VALUE NUMBER(14,3),
                     BY_PROD_VALUE NUMBER(14,3),
                     TOT_MAT_COST NUMBER(14,3),
                     TOT_OVER_HEADS NUMBER(14,3),
                     BOM_CODE CHAR(10),
                     CONSUME_QTY NUMBER(14,3),      
                     PROD_COMPLETED_PERC NUMBER(14,3),
                     WORDER_RCP_DATE  DATE)
      and table type as
      
      CREATE OR REPLACE
      TYPE TEST_DWH_PROD_TAB AS TABLE OF TEST_DWH_PROD
      now i need to implement the below code in the function
      BEGIN
      
      --------------------GETTING QUARTER NO.------------------------------
      
      V_QTY:='SELECT TO_NUMBER(TO_CHAR(TO_DATE('''||V_REPORT_DATE||'''), ''Q'')) FROM DUAL';
      --DBMS_OUTPUT.PUT_LINE(V_QTY);
      EXECUTE IMMEDIATE V_QTY INTO V_QUARTER_NUMBER ; 
      --DBMS_OUTPUT.PUT_LINE('QT NUM'||V_QUARTER_NUMBER);
      
      --------------------CURRENT QUARTER VALUE------------------------------
      
      SELECT MIN (t), MAX (LAST_DAY (t)) INTO V_START_DATE,V_LAST_DATE
              FROM(SELECT ADD_MONTHS (TRUNC(TO_DATE(V_REPORT_DATE), 'YYYY'), LEVEL - 1) t,
                  TO_CHAR (ADD_MONTHS (TRUNC(TO_DATE(V_REPORT_DATE), 'YYYY'), LEVEL - 1), 'Q')QTD
              FROM DUAL
                     CONNECT BY LEVEL <= 12) A
                       WHERE A.QTD =  V_QUARTER_NUMBER;
                     --DBMS_OUTPUT.PUT_LINE(V_START_DATE||'****'||V_LAST_DATE);
       
      V_CURRQTD:='SELECT SUM('||V_COL_NAME||')  FROM  '||V_TAB_NAME|| ' WHERE '||V_FILTERCOL||' BETWEEN   TO_DATE('''||V_START_DATE||''') ' || '  AND  TO_DATE('''||V_REPORT_DATE||''')';
      
      --DBMS_OUTPUT.PUT_LINE(V_CURRQTD);
      EXECUTE IMMEDIATE V_CURRQTD INTO V_SUM;
      --DBMS_OUTPUT.PUT_LINE(V_SUM);
      V_COL_NAME=ACTUAL_YIELD 
      V_FILTERCOL=WO_RELEASE_DATE 
      OF TYPE
      the above query is giving the value of current QTD(on the basis of V_REPORT_DATE

      I need to integrate the same as user want the output as table type and he can also give some filter conditions.

      Help appreciated

      Thnx in advance.
        • 1. Re: needed help in table function
          L-MachineGun
          977490 wrote:
          Hi experts,
          . . . Etc . . .
          i need to implement the below code in the function
          . . . Etc . . .
          the above query is giving the value of current QTD(on the basis of V_REPORT_DATE

          I need to integrate the same as user want the output as table type and he can also give some filter conditions.

          Help appreciated
          Please clarify:

          * implement the below code in the function? - what function?
          * I need to integrate the same? - integrate where?
          * user want the output as table type? - what output?
          * he can also give some filter conditions? - Give to whom?
          :p
          • 2. Re: needed help in table function
            User286067
            L-MachineGun wrote:
            Please clarify:

            * implement the below code in the function? - what function?
            * I need to integrate the same? - integrate where?
            * user want the output as table type? - what output?
            * he can also give some filter conditions? - Give to whom?
            :p
            If in the past you worked with pipelined functions? if so you probably could recognize the question.
            :p
            • 3. Re: needed help in table function
              L-MachineGun
              rjamya wrote:

              If in the past you worked with pipelined functions? if so you probably could recognize the question.
              Very difficult to recognize anything when the information supplied is incomplete and not understandable.
              Perhaps you need to learn how to post a question in a forum, be sure to read the forum's FAQ
              or also: SQL and PL/SQL FAQ
              ; )
              • 4. Re: needed help in table function
                rp0428
                >
                now i need to implement the below code in the function
                . . .
                the above query is giving the value of current QTD(on the basis of V_REPORT_DATE

                I need to integrate the same as user want the output as table type and he can also give some filter conditions.
                >
                There is no 'TABLE' to return. Your query returns a single SCALAR value.

                And if that above query is doing nothing more than determining the start of the quarter for V_REPORT_DATE there it is far more complex than needed.

                The TRUNC function can do that by itself
                select trunc(sysdate, 'q'), sysdate from dual
                
                TRUNC(SYSDATE,'Q'),SYSDATE
                10/1/2012,12/28/2012 4:10:12 PM
                So you can replace ALL of that code with this
                V_CURRQTD:='SELECT SUM('||V_COL_NAME||')  FROM  '||V_TAB_NAME|| ' WHERE '||V_FILTERCOL||' 
                BETWEEN   TRUNC(TO_DATE('''||V_START_DATE||'''), 'q') ' || '  AND  TO_DATE('''||V_REPORT_DATE||''')';
                You need to provide the DDL for the code you are using and a better explanation of what you are trying to do.
                • 5. Re: needed help in table function
                  980493
                  No, I never worked with pipelined function.....

                  I've wrote a function to get the sum of QTD, PRVQTD, YTD, PRVYTD and PRVYRSAMEMONTH on the basis of given table and columns as in parameter....But as the function return only one value, I was told to integrate the same function into table pipelined function so the output come as in tabular form, and he the user(layman) who'll generate the report from the BI interface can able to make group by and fire some filter conditions on the output comes from table pipelined function.

                  Below is the function I need integrate in table pipelined function:
                  CREATE OR REPLACE FUNCTION Test_Qtd_Ytd_Currqtd_Sum_Chk15(V_TBL_NAME IN VARCHAR2,V_COL_NAM IN VARCHAR2,V_RPRT_DATE VARCHAR2, V_FLTRCOL IN VARCHAR2,VALUE_OF IN VARCHAR2)
                  RETURN NUMBER  IS 
                  
                          A_RET_VALUE NUMBER;
                  
                  V_REPORT_DATE DATE:=V_RPRT_DATE;
                  V_FILTERCOL VARCHAR2(30):=UPPER(V_FLTRCOL);
                  V_COL_NAME VARCHAR2(30):=UPPER(V_COL_NAM);
                  V_TAB_NAME VARCHAR2(30):=UPPER(V_TBL_NAME);
                  V_VALUE_TYPE VARCHAR2(8):=UPPER(VALUE_OF);
                  V_QUARTER_NUMBER  NUMBER(1);
                  V_QTY VARCHAR2(1000);
                  V_CURRQTD_STRT VARCHAR2(11);
                  V_CURRQTD_END  VARCHAR2(11);
                  V_CURRQTD VARCHAR2(1000);
                  V_PRVQTD_STRT VARCHAR2(11);
                  V_PRVQTD_END VARCHAR2(11);
                  V_PRVQTD VARCHAR2(1000);
                  V_CURRYTD_STRT VARCHAR2(11);
                  V_CURRYTD_END VARCHAR2(11);
                  V_CURR_YTD VARCHAR2(1000);
                  V_PRVYTD VARCHAR2(11);
                  V_PRVYTD_STRT VARCHAR2(11);
                  V_PRVYTD_END VARCHAR2(11);
                  V_PRV_YTD VARCHAR2(1000);
                  V_PRVYRMTH VARCHAR2(11);
                  V_PRVMTH_STRT VARCHAR2(11);
                  V_PRVMTH_END VARCHAR2(11);
                  V_PRVYR_MTH VARCHAR2(1000);
                  V_CURRQTD_SUM NUMBER(14,3):=0;
                  V_PRVQTD_SUM NUMBER(14,3):=0;
                  V_CURRYTD_SUM NUMBER(14,3):=0;
                  V_PRVYTD_SUM NUMBER(14,3):=0;
                  V_PRVYRMTH_SUM NUMBER(14,3):=0;
                  
                  
                  
                  BEGIN
                  
                  --------------------GETTING QUARTER NO.------------------------------
                  
                  V_QTY:='SELECT TO_NUMBER(TO_CHAR(TO_DATE('''||V_RPRT_DATE||'''), ''Q'')) FROM DUAL';
                  DBMS_OUTPUT.PUT_LINE(V_QTY);
                  EXECUTE IMMEDIATE V_QTY INTO V_QUARTER_NUMBER ; 
                  DBMS_OUTPUT.PUT_LINE('QT NUM'||V_QUARTER_NUMBER);
                  
                  --------------------CURRENT QUARTER VALUE------------------------------
                  SELECT   MIN (t)"CURNT_QTR_ST_DT", MAX (LAST_DAY (t))"CURNT_QTR_END_DT", ADD_MONTHS(MIN (t),-3)"PREV_QTR_ST_DT", ADD_MONTHS(MAX (LAST_DAY (t)),-3) "PREV_QTR_END_DT" INTO V_CURRQTD_STRT ,V_CURRQTD_END,V_PRVQTD_STRT, V_PRVQTD_END
                  FROM (SELECT ADD_MONTHS (TRUNC(TO_DATE(TO_DATE(V_RPRT_DATE)), 'YYYY'), LEVEL - 1)  t,
                        TO_CHAR (ADD_MONTHS (TRUNC(TO_DATE(TO_DATE(V_RPRT_DATE)), 'YYYY'), LEVEL - 1), 'Q')QTD
                        FROM DUAL
                         CONNECT BY LEVEL <= 12) A
                         WHERE A.QTD  = V_QUARTER_NUMBER;
                  
                  V_CURRQTD:='SELECT SUM('||V_COL_NAME||')  FROM  '||V_TAB_NAME|| ' WHERE '||V_FILTERCOL||' BETWEEN   TO_DATE('''||V_CURRQTD_STRT||''') ' || '  AND  TO_DATE('''||V_REPORT_DATE||''')';
                  
                  DBMS_OUTPUT.PUT_LINE(V_CURRQTD);
                  EXECUTE IMMEDIATE V_CURRQTD INTO V_CURRQTD_SUM;
                  DBMS_OUTPUT.PUT_LINE(V_CURRQTD_SUM);
                  
                  --------------------END OF CURRENT QUARTER------------------------------
                  
                  --------------------PREVIOUS QUARTER------------------------------------
                  
                  V_PRVQTD:='SELECT SUM('||V_COL_NAME||')  FROM  '||V_TAB_NAME|| ' WHERE '||V_FILTERCOL||' BETWEEN   TO_DATE('''||V_PRVQTD_STRT||''') ' || '  AND  TO_DATE('''||V_PRVQTD_END||''')';
                  DBMS_OUTPUT.PUT_LINE(V_PRVQTD);
                  EXECUTE IMMEDIATE V_PRVQTD INTO V_PRVQTD_SUM;
                  DBMS_OUTPUT.PUT_LINE(V_PRVQTD_SUM);
                  
                  --------------------END OF PREVIOUS QUARTER------------------------------
                  
                  --------------------CURRENT YTD-------------------------------------------
                  
                  SELECT TRUNC(TO_DATE(V_RPRT_DATE),'YEAR')INTO V_CURRYTD_STRT FROM DUAL;
                  
                  SELECT LAST_DAY (ADD_MONTHS (TO_DATE (V_CURRYTD_STRT), 11)) INTO V_CURRYTD_END FROM DUAL;
                  
                  V_CURR_YTD:='SELECT SUM('||V_COL_NAME||')  FROM  '||V_TAB_NAME|| ' WHERE '||V_FILTERCOL||' BETWEEN   TO_DATE('''||V_CURRYTD_STRT||''') ' || '  AND  TO_DATE('''||V_REPORT_DATE||''')';
                  DBMS_OUTPUT.PUT_LINE(V_CURR_YTD);
                  EXECUTE IMMEDIATE V_CURR_YTD INTO V_CURRYTD_SUM;
                  DBMS_OUTPUT.PUT_LINE(V_CURRYTD_SUM);
                  
                  --------------------END OF CURRENT YTD------------------------------------
                  
                  --------------------PREVIOUS YTD------------------------------------------
                  
                  SELECT ADD_MONTHS(TO_DATE(V_RPRT_DATE),-12) INTO V_PRVYTD FROM DUAL;
                  
                  SELECT TRUNC(TO_DATE(V_PRVYTD),'YEAR') INTO V_PRVYTD_STRT FROM DUAL;
                  
                  SELECT LAST_DAY (ADD_MONTHS (TO_DATE (V_PRVYTD_STRT), 11)) INTO V_PRVYTD_END FROM DUAL;
                  
                  V_PRV_YTD:='SELECT SUM('||V_COL_NAME||')  FROM  '||V_TAB_NAME|| ' WHERE '||V_FILTERCOL||' BETWEEN   TO_DATE('''||V_PRVYTD_STRT||''') ' || '  AND  TO_DATE('''||V_PRVYTD_END||''')';
                  DBMS_OUTPUT.PUT_LINE(V_PRV_YTD);
                  EXECUTE IMMEDIATE V_PRV_YTD INTO V_PRVYTD_SUM;
                  DBMS_OUTPUT.PUT_LINE(V_PRVYTD_SUM);
                  
                  --------------------END OF PREVIOUS YTD--------------------------------------
                  
                  --------------------PREVIOUS YR SAME MONTH-----------------------------------
                  
                  SELECT ADD_MONTHS(V_REPORT_DATE,-12) INTO V_PRVYRMTH FROM DUAL;-----PREVIOUS YR SAME DATE
                  
                  SELECT TRUNC(TO_DATE(V_PRVYRMTH),'MM')INTO V_PRVMTH_STRT FROM DUAL;------1ST DATE OF PRV YR SAME MONTH
                  
                  WITH INP AS
                  
                  (SELECT TO_DATE(V_PRVMTH_STRT) INP FROM DUAL)
                  
                  SELECT LAST_DAY(INP) INTO  V_PRVMTH_END FROM INP;---------LAST DATE OF PRV YR SAME MONTH
                  
                  V_PRVYR_MTH:='SELECT SUM('||V_COL_NAME||')  FROM  '||V_TAB_NAME|| ' WHERE '||V_FILTERCOL||' BETWEEN   TO_DATE('''||V_PRVMTH_STRT||''') ' || '  AND  TO_DATE('''||V_PRVMTH_END||''')';
                  DBMS_OUTPUT.PUT_LINE(V_PRVYR_MTH);
                  EXECUTE IMMEDIATE V_PRVYR_MTH INTO V_PRVYRMTH_SUM;
                  DBMS_OUTPUT.PUT_LINE(V_PRVYRMTH_SUM);
                  
                  --------------------END OF PREVIOUS YR SAME MONTH-----------------------------------
                  
                  
                  --------------------IF CODN. TO GET THE VALUES AS GIVEN DURATION-------------
                  
                  DBMS_OUTPUT.PUT_LINE('CURR_QTD'||'          '||'PRVQTD'||'         '||'CURR_YTD'||'           '||'PRV_YTD'||'           '||'PRVYRMTH');
                  DBMS_OUTPUT.PUT_LINE(V_CURRQTD_SUM||'    '||V_PRVQTD_SUM||'      '||V_CURRYTD_SUM||'    '||V_PRVYTD_SUM||'        '||V_PRVYRMTH_SUM);
                  
                  IF(VALUE_OF = 'CURRQTD') THEN
                     A_RET_VALUE := V_CURRQTD_SUM;
                  ELSIF(VALUE_OF = 'PRVQTD') THEN
                     A_RET_VALUE := V_PRVQTD_SUM;
                  ELSIF (VALUE_OF = 'CURRYTD') THEN
                     A_RET_VALUE := V_CURRYTD_SUM;
                  ELSIF (VALUE_OF = 'PRVYTD') THEN
                     A_RET_VALUE := V_PRVYTD_SUM;
                  ELSIF (VALUE_OF = 'PRVYRMTH') THEN
                     A_RET_VALUE := V_PRVYRMTH_SUM;
                  END IF;
                  
                  --------------------END OF IF CONDITION--------------------------------------
                  
                  RETURN A_RET_VALUE;   
                  
                  END Test_Qtd_Ytd_Currqtd_Sum_Chk15;
                  /
                  Thnx in advance for the help.....
                  • 6. Re: needed help in table function
                    rp0428
                    >
                    But as the function return only one value, I was told to integrate the same function into table pipelined function so the output come as in tabular form, and he the user(layman) who'll generate the report from the BI interface can able to make group by and fire some filter conditions on the output comes from table pipelined function.
                    >
                    Reread my reply.

                    There is NOTHING to pipeline: your function only returns ONE value. So there is also nothing to group by.
                    >
                    the function I need integrate in table pipelined function:
                    >
                    That function doesn't even work so you should not integrate it into anything. The code you posted can't possibly work. There are variables defined (e.g. V_PRVQTD_STRT) that are never populated but you try to use them in a query.

                    As I ask you need to provide an example of the input you intend to provide and the output you expect to get from that input.
                    You need to refocus on defining the PROBLEM. Right now you are focused on the solution that you seem intent on wanting to use.