oracle count

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

This may be more of a sql question. Here is my code without my dbi connect.

 my $query = "SELECT DISTINCT a.vol_id, a.vol_nbr, 
                              a.vol_mnt_req, a.vol_ser         
                              c.vol_juke, c.vol_stat
                    FROM 
                        (select * from volume_index) a,
                        (select * from volume_queue) c
                    WHERE a.vol_id = c.vol_id 
                    AND a.vol_mnt_req is not null 
                    AND c.vol_stat != 'M' 
                    OR a.vol_mnt_req is not null 
                    AND a.vol_nbr != '1'";
        my $data = send_recv($query);
        
        my (@rows) = split(/\n/, $data);
        foreach my $rows (@rows) {  
          my ($id,$vnbr,$req,$ser,$juke,$stat) = split(/;/, $rows);
              print "$label $volnbr\n";
        }
[download]

This works exactly how i want, but i like to have a count of total number of rows in the same query. Here is a few things of tried:
select distinct count(*) then the rest of the select statment.

from (select count(*) from volume_index) a,
(select * from volume_index) a,
(select * from volume_queue) c
Both failed. any help would be appreciated.

Comment on oracle count Download Code

Replies are listed 'Best First'.
Re: oracle count by crashtest (Curate) on Apr 27, 2006 at 00:47 UTC
I'm not clear what you're looking for when you say "a count of total number of rows". Do you want to know how many rows were returned in your resultset? In that case, I'd suggest just checking how many elements you have in your `@rows` array, i.e.: `my (@rows) = split(/\n/, $data); my $number_of_rows = @rows;` [download] If, on the other hand, you want to know how many total rows are in the `volume_index` table (as your second attempt seems to indicate), you should probably issue another query: `my $number_of_rows = send_recv("SELECT COUNT(*) FROM volume_index");` [download] Hope this helps.	[reply] [d/l] [select]
Re: oracle count by aufflick (Deacon) on Apr 27, 2006 at 01:43 UTC
You can find out how many rows a DBI statement returned using the `$sth->rows` method. Note that DBI only promises that this will be accurate after you have retrieved all the rows. Whether it is reliable before then is probably database and DBD driver dependant.	[reply] [d/l]
Re: oracle count by MFDoom (Novice) on Apr 27, 2006 at 01:12 UTC
I think something like this should work out. I've never tried doing a count over specific tables, but that shouldn't matter. `"SELECT COUNT(DISTINCT a.vol_id, a.vol_nbr, a.vol_mnt_req, a.vol_ser c.vol_juke, c.vol_stat) FROM (select * from volume_index) a, (select * from volume_queue) c WHERE a.vol_id = c.vol_id AND a.vol_mnt_req is not null AND c.vol_stat != 'M' OR a.vol_mnt_req is not null AND a.vol_nbr != '1'"` [download] Plans failed fully because you failed to fully plan.	[reply] [d/l]