I can't display data form a variable [message #35754] |
Fri, 12 October 2001 06:16 |
Quique
Messages: 1 Registered: October 2001
|
Junior Member |
|
|
Hi,
I'm new at this, and I;m trying to get a Count(*) daily from a table during one period, but I cannot display the date this is my code:
DECLARE
fecha DATE := '01-OCT-2000';
total NUMBER(3) := 0;
BEGIN
WHILE fecha < LAST_DAY('01-OCT-2000') LOOP
SELECT Count(*) INTO total FROM PO.PO_Headers_All ph WHERE ph.Created_By = 3671 AND ph.Creation_Date = fecha;
fecha := fecha + 1;
dbms_output.put_line(total);
END LOOP;
END;
I hope someone can help me.
----------------------------------------------------------------------
|
|
|
Re: I can't display data form a variable [message #35755 is a reply to message #35754] |
Fri, 12 October 2001 06:25 |
Suresh Vemulapalli
Messages: 624 Registered: August 2000
|
Senior Member |
|
|
DECLARE
fecha DATE := to_date('01-OCT-2000','dd-mon-yyyy');
total NUMBER(3) := 0;
BEGIN
WHILE fecha < LAST_DAY(fecha) LOOP
SELECT Count(*) INTO total FROM PO.PO_Headers_All ph WHERE ph.Created_By = 3671 AND trunc(ph.Creation_Date) = trunc(fecha);
fecha := fecha + 1;
dbms_output.put_line(total);
END LOOP;
END;
----------------------------------------------------------------------
|
|
|
Re: I can't display data form a variable [message #35761 is a reply to message #35755] |
Fri, 12 October 2001 09:25 |
m
Messages: 15 Registered: April 2001
|
Junior Member |
|
|
Suresh: your answer will not perform a count on the last day of the month
"WHILE fecha < LAST_DAY(fecha) LOOP"
-- WHILE 31-OCT-2001 < LAST_DAY(31-OCT-2001) LOOP
-- the above will not perform a count on 31-OCT-2001
code should be:
WHILE fecha <= LAST_DAY(to_date('01-OCT-2000','dd-mon-yyyy')) LOOP
or an additional variable used for last day:
DECLARE
fecha DATE := to_date('01-OCT-2000','dd-mon-yyyy');
last_fecha DATE := LAST_DAY(to_date('01-OCT-2000','dd-mon-yyyy'));
...
BEGIN
...
WHILE fecha <= last_fecha LOOP
...
Also the dbms_output statement needs to perform to_char function:
dbms_output.put_line(total);
dbms_output.put_line('Date: ' || TO_CHAR(fetcha, 'DD-MON-YYYY'') || ' COUNT: ' || TO_CHAR(total));
----------------------------------------------------------------------
|
|
|