1 Reply Latest reply on Jun 28, 2013 6:05 AM by Marwim

    looping through records

    DisolocatedCurry

      I have the table below. I want to loop through the records and update column "CUML_UNITS_TILL_POSITIVE".

       

      I want to start at the latest "YEAR_CD". So 2013 in this dataset.

       

      Then I will declare a variable called "X_CUML_UNITS_TILL_POSITIVE" set to be 0 initially.

       

      I want to update "CUML_UNITS_TILL_POSITIVE" for 2013 to be the "CLOSING_UNITS" + "X_CUML_UNITS_TILL_POSITIVE"  for that year if the "X_CUML_UNITS_TILL_POSITIVE" are <0 else set equal to "CLOSING_UNITS".

       

      Then I will set "X_CUML_UNITS_TILL_POSITIVE" = "CUML_UNITS_TILL_POSITIVE" SO THAT IT IS ALWAYS LAST "CUML_UNITS_TILL_POSITIVE"

       

      The code I tried to use in SQL Developer is: Its doing anything. Please help if you can?

       

      DECLARE 
        X_CUML_UNITS_TILL_POSITIVE NUMBER := 0;
       
       
      BEGIN 
        FOR i IN '2013'..'1997' LOOP 
            IF i<='2013' AND X_CUML_UNITS_TILL_POSITIVE >= 0 THEN   
              UPDATE CPC_ABS_UL_YEARLY_BREAKDOWN
              SET CUML_UNITS_TILL_POSITIVE = closing_units;
            ELSE
              UPDATE CPC_ABS_UL_YEARLY_BREAKDOWN
              SET CUML_UNITS_TILL_POSITIVE = closing_units;
              END IF; 
            X_CUML_UNITS_TILL_POSITIVE := X_CUML_UNITS_TILL_POSITIVE + CUML_UNITS_TILL_POSITIVE
        END LOOP; 
        COMMIT; 
      END;

       

       

      So the table should look like....below after the update:

       

      YEAR_CDCLOSING_UNITSCUML_UNITS_TILL_POSITIVE
      1997146135.68123398.5495
      1998-4584.5098-22737.1305
      1999-9329.0706-18152.6207
      2000-8823.5501-8823.5501
      200110000072908.3747
      2002-7396.2359-27091.6253
      2003-7058.8679-19695.3894
      2004-6452.0977-12636.5215
      2005-6184.4238-6184.4238
      200610000064550.0717
      2007-5661.8467-35449.9283
      2008-5666.7991-29788.0816
      2009-6016.8767-24121.2825
      2010-6000.4204-18104.4058
      2011-6063.9903-12103.9854
      2012-4224.0521-6039.9951
      2013-1815.943-1815.943