PL/SQL While Loop

Question

I am a beginner in PL/SQL. I want to run simple WHILE LOOP and I am receiving errors.

Here is my code:

DECLARE
  counter INTEGER := 01;
BEGIN
  WHILE counter <= 30 
  LOOP
    SELECT name, count(iid) as Counts
    FROM table.orders
    WHERE date_inserted >= TO_DATE('14-SEP-16 12.00.00 AM','DD-MON-YY HH.MI.SS AM')
    AND date_inserted <= TO_DATE('14-SEP-16 12.'counter'.00 PM','DD-MON-YY HH.MI.SS AM')
    HAVING count(iid) >= 60
    ORDER BY count(iid) DESC;
  END LOOP;
END;

I am getting these errors when running the code:

*Error starting at line : 1 in command -
DECLARE
  counter INTEGER := 01;
BEGIN
  WHILE counter <= 30 
  LOOP
    SELECT iname, count(iid) as Counts
    FROM table.orders
    WHERE date_inserted >= TO_DATE('14-SEP-16 12.00.00 AM','DD-MON-YY HH.MI.SS AM')
    AND date_inserted <= TO_DATE('14-SEP-16 12.'counter'.00 PM','DD-MON-YY HH.MI.SS AM')
    HAVING count(iid) >= 60
    ORDER BY count(iid) DESC;
  END LOOP;
END;

Error report - ORA-06550: line 9, column 49:
PL/SQL: ORA-00907: missing right parenthesis
ORA-06550: line 6, column 5:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
Cause: Usually a PL/SQL compilation error.
Action:

What seems to be the problem? Thanks in advance!

Answer 1

Don't concatenate, it would be more efficient (and in my opinion easier to read) if you simply add the counter to a proper date value. You also don't increment the counter and you are missing the group by in the query. And finally, the result of a query must be stored somewhere.

DECLARE
  counter INTEGER := 1;
  l_from_date DATE;
  l_name varchar(200);
  l_count integer;
BEGIN
  from_date := TO_DATE('14-SEP-16 12.00.00 AM','DD-MON-YY HH.MI.SS AM');

  WHILE counter <= 30 
  LOOP
    SELECT name, count(iid) as Counts
       into l_name, l_count --<< store the result somewhere
    FROM table.orders 
    WHERE date_inserted >= l_from_date 
    AND date_inserted <= l_from_date + counter
    GROUP BY name --<< this was missing as well
    HAVING count(iid) >= 60
    ORDER BY count(iid) DESC;

    counter := counter + 1;  --<< increment the counter

    -- do something with l_name and l_count ....
  END LOOP;
END;
/

---

Unrelated to this question: but you should avoid NLS specific format strings for date values. Better use a format that doesn't depend on the language used by the SQL client (or application) running the code. e.g. to_date('2016-09-14 00:00:00', 'yyyy-mm-dd hh24:mi:ss')

Answer 2

You would want to use CONCAT or || after converting counter to char in order to add it in between those strings.

Answer 3

In your code, I don't see you increment the counter. Is it by design??

To answer your question, as mentioned in comments, you would want to use concat for concatenating the string. Change your where condition to

WHERE date_inserted >= TO_DATE('14-SEP-16 12.00.00 AM','DD-MON-YY HH.MI.SS AM')
AND date_inserted <= TO_DATE('14-SEP-16 12.'||to_char(counter)||'.00 PM','DD-MON-YY HH.MI.SS AM')

In fact in Oracle 11 or newer versions (may be previous too but I never worked on those), you don't need to do conversions explicitly. So you can directly concatenate counter to the string.

WHERE date_inserted >= TO_DATE('14-SEP-16 12.00.00 AM','DD-MON-YY HH.MI.SS AM')
AND date_inserted <= TO_DATE('14-SEP-16 12.'||counter||'.00 PM','DD-MON-YY HH.MI.SS AM')

Answer 4

You can concatenate the strings directly

WHERE date_inserted >= TO_DATE('14-SEP-16 12.00.00 AM','DD-MON-YY HH.MI.SS AM')
AND date_inserted <= TO_DATE('14-SEP-16 12.'||counter||'.00 PM','DD-MON-YY HH.MI.SS AM')