userimage

Correctly looping through a data set, and displaying, or outputting all results, WITHOUT using a Temporary Table

I have some PL/SQL code that is intended to loop through the entire month range for each month in each year, and then retrieve the number of times something occurred within a monthly period.

Right now, I"m not doing a nested loop for each year that exists because I need to properly understand how this works before continuing.

Here"s my code:

BEGIN

   FOR i IN 1..11 LOOP

        BEGIN

            SELECT COUNT(UNIQUE EMPLOYEE) as Emp FROM (SELECT DATE_COL, EMPLOYEE

                FROM CORE.DATE_TEST

            WHERE DATE_COL  >= TO_DATE(i || "/1/2015 00:00:00", "MM/DD/YYYY HH24:MI:SS")

                AND DATE_COL  < TO_DATE((i + 1) || "/1/2015 23:59:59", "MM/DD/YYYY HH24:MI:SS")

            ORDER BY DATE_COL ASC);

        END;

   END LOOP;

   COMMIT;

END;

And I"m getting this error: PLS-00428: an INTO clause is expected in this SELECT statement

Obviously, I need to select the results into something, but what that something is, I"m not quite sure. This may not even be the right way to do it.

However, I"d like to take all of these results, and combine them into a single table like this:

+----------+-------+

|   date   | count |

+----------+-------+

| January  |  200  |

+----------+-------|

| February |  100  |

+----------+-------|

| March    |  500  |

+----------+-------|

And so on.

 

userimage

This will print your query.

DECLARE

    V_number varchar2(100);

    BEGIN

       FOR i IN 1..11 LOOP

                     SELECT COUNT(UNIQUE EMPLOYEE) AS EMP INTO V_number FROM (SELECT CURRENT_DATE, EMPLOYEE

                            FROM CORE.DATE_TEST

                    WHERE CURRENT_DATE  >= TO_DATE(i || '/1/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS')

                            AND CURRENT_DATE  < TO_DATE((i + 1) || '/1/2015 23:59:59', 'MM/DD/YYYY HH24:MI:SS')

                    ORDER BY CURRENT_DATE ASC);

                    dbms_output.put_line(TO_CHAR(TO_DATE(I || '/1/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Month')||'  '||V_NUMBER);     

       END LOOP;

    END;

Answer is