Home » SQL & PL/SQL » SQL & PL/SQL » I can't display data form a variable
I can't display data form a variable [message #35754] Fri, 12 October 2001 06:16 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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));

----------------------------------------------------------------------
Previous Topic: utl_file question
Next Topic: Assign output value from package to bourne shell variable
Goto Forum:
  


Current Time: Sat May 25 03:20:42 CDT 2024