Home » SQL & PL/SQL » SQL & PL/SQL » counting within cursor loops (merged)
counting within cursor loops (merged) [message #407414] Wed, 10 June 2009 02:28 Go to next message
MANASH
Messages: 7
Registered: June 2009
Junior Member
Dear all
How can i count the number of rows returned by a select query
called within a cursor.
For example:

A cursor Cur1 is declared
A Rec1 Cur1%rowtype is declared
A variable for counting counter is declared as integer

For Rec1 in Cur1
Loop
begin
select count(*) into counter
from table1
where table1.field_A=Rec1.field1
and table1.field_B=Rec1.field2
end
DBMS.OUTPUT_PUTLINE
(
counter
);
End loop


DEAR ALL
IN THE ABOVE SCENARIO
IN CASE WHEN THE VALUE IN REC1.FIELD1 IS UNIQUE OR REC1.FIELD2 IS UNIQUE, THE VALUE OF COUNTER IS 1
BUT WHEN
THE RECORDSET IS NOT UNIQUE , THE VALUE OF THE COUNTER MUST BE MORE THAN 1
BUT I AM GETTING VALUE 1 FOR VARIABLE COUNTER IN ALL CASES IRRESPECTIVE OF THE INPUT
IS THERE ANY MISTAKE IN MY LOGIC
please HELP!

REGARDS
MANASH..............


Re: counting within cursor loops [message #407422 is a reply to message #407414] Wed, 10 June 2009 02:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please do not post in all uppercase. It is the equivalent of shouting.

You are making a mistake if you thing that the count will return 1 if either field 1 or field 2 is unique. It will return 1 if the combination of field 1 and field 2 is unique.

Your code, by the way, was manipulated to the extent that it is not valid code anymore (probably obfuscating your production code, which is fine, but you overdid it)
Re: counting within cursor loops [message #407424 is a reply to message #407414] Wed, 10 June 2009 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide.
Among other things "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
counting within cursor loops [message #407494 is a reply to message #407414] Wed, 10 June 2009 06:06 Go to previous messageGo to next message
MANASH
Messages: 7
Registered: June 2009
Junior Member
Dear all
please look into the attached query
DECLARE
   sol1        htd.sol_id%TYPE      := &&1;
   fromdate    htd.tran_date%TYPE   := &&2;
   todate      htd.tran_date%TYPE   := &&3;

   CURSOR cur1
   IS
      SELECT dst.issu_br_code, dst.dd_num, dst.issue_date, dst.dd_crncy_code,
             dst.dd_amt, dst.payee_name, dst.pur_name, ddc.tran_id,
             ddc.tran_date
        FROM dst, ddc
       WHERE dst.dd_num = ddc.dd_num
         AND dst.issue_date = ddc.tran_date
         AND ddc.comm_amt_actual < ddc.comm_amt_calc
         AND ddc.init_sol_id = sol1
         AND ddc.tran_date BETWEEN fromdate AND todate;

   comm        htd.tran_amt%TYPE;
   mycounter   INTEGER              := 0;
   rec1        cur1%ROWTYPE;
BEGIN
   FOR rec1 IN cur1
   LOOP
      BEGIN
         BEGIN
            SELECT htd.tran_amt
              INTO comm
              FROM htd
             WHERE TRIM (htd.tran_id) = TRIM (rec1.tran_id)
               AND htd.tran_date = rec1.tran_date
               AND htd.acid = (SELECT acid
                                 FROM gam
                                 WHERE sol_id = sol1 AND 
                                 bacid = 'COMDRAFT');
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               comm := 0;

               SELECT COUNT (htd.part_tran_srl_num)
                 INTO mycounter
                 FROM htd
                 WHERE TRIM (htd.tran_id) = TRIM (rec1.tran_id)
                   AND htd.tran_date = rec1.tran_date
                   AND htd.acid IN (SELECT acid
                                   FROM gam
                                   WHERE schm_type= 'DDA');         
         END;

         DBMS_OUTPUT.put_line (   rec1.issue_date
                               || '|'
                               || rec1.dd_num
                               || '|'
                               || rec1.payee_name
                               || '|'
                               || rec1.dd_amt
                               || '|'
                               || rec1.dd_crncy_code
                               || '|'
                               || rec1.pur_name
                               || '|'
                               || comm
                               || '|'
                               || sol1
                               || '|'
                               || fromdate
                               || '|'
                               || todate
                               || '|'
                               || mycounter
                              );
      END;
   END LOOP;
END;
/

The select *(htd.part_tran_srl_num)
INTO mycounter.............
statement in the query doesn't seem to be working .
The value of mycounter is the number of records returned by the query and can be more than 1 in some case.
But the value of mycounter is being returned as 1 in all cases irrespective of the number of record the query returns.
Is the Count() function not working inside the cursor loop or is the logic wrong.
In that case how can i count the number of records.


regards
Manash..
Re: counting within cursor loops [message #407498 is a reply to message #407494] Wed, 10 June 2009 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not start a new topic, continue on the previous one.
I merge the 2.

Regards
Michel
Re: counting within cursor loops [message #407504 is a reply to message #407498] Wed, 10 June 2009 06:21 Go to previous messageGo to next message
MANASH
Messages: 7
Registered: June 2009
Junior Member
Dear Michel
thanks for your response but i stil don't have the solution

Regards
Manash........
Re: counting within cursor loops (merged) [message #407531 is a reply to message #407414] Wed, 10 June 2009 07:16 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
If want to count records use count(*).

You do realize that the query to populate mycounter only runs if the previous query doesn't find any data.

And since you're not reinitializing mycounter on every iteration of the loop if it doesn't run it'll still have the value from the last time that query was executed.
Re: counting within cursor loops (merged) [message #407537 is a reply to message #407531] Wed, 10 June 2009 07:32 Go to previous messageGo to next message
MANASH
Messages: 7
Registered: June 2009
Junior Member
Sorry about the indentation , but the query to populate the mycounter does execute in all the cases.
i do accept the fact that mycounter is not being reinitialised ,
but i think there is some problem with counting within the loop in a cursor set.
Is there some different syntax or login to count the no of records in a cursor???
Re: counting within cursor loops (merged) [message #407551 is a reply to message #407537] Wed, 10 June 2009 08:05 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No, there is no change in syntax.
There is no problem with performing Select statements from within a cursor loop.

If the value of mycounter is showing as 1 for each row in the cursor, then what this means is that for every row where the first SELECT returns no rows, the SELECT in the Exception handler returns a single row.
Previous Topic: TEMP table space
Next Topic: schema level trigger
Goto Forum:
  


Current Time: Sat Dec 03 09:57:09 CST 2016

Total time taken to generate the page: 0.10277 seconds