Re: Help... How Do I?
Date: 2000/05/14
Message-ID: <shu65chbje421_at_corp.supernews.com>#1/1
"Harvey" <harveyb_at_erols.com> wrote in message
news:391E151D.93FA2B9F_at_erols.com...
> Hi All!
>
> I'm trying to figure out exactly how to get this query written, and hope
> someone out here can give me a hand. First a description of what I'd
> like to be able to do:
>
> I have a table in our database that keeps track of how many sessions
> are open and how many user licenses are in use at any given time. What
> I'd like to be able to do is do a count of that table where a specific
> condition exists (that's not a problem at all) and then take the results
> of that count and make it part of an insert command into another table
> (that's what I don't know how to do). Logically it would flow like
> this:
>
> select count(*) from seat_assignment where s_add_on_cd != 'N/A';
> insert into stats (logged_on, system_date) values ('<results of the
> above count>', sysdate)
>
> A case of knowing where I want to get to, but not knowing the path to
> get there.
>
> Thanks in Advance!
>
> Harvey
You can use PL/SQL to do it (handy if you want to evaluate if records were found or not and/or if the count was zero or whatever) or SQL (easiest but little/no conditional control unless you use DECODE):
SQL:
insert into stats (logged_on, system_date)
select count(*), sysdate
from seat_assignment
where s_add_on_cd != 'N/A';
PL/SQL (implicit cursor):
declare V_COUNT number := 0;
begin
select count(*)
into V_COUNT
from seat_assignment
where s_add_on_cd != 'N/A'';
insert into stats (logged_on, system_date)
values(V_COUNT, sysdate);
end;
PL/SQL (explicit cursor):
declare
V_COUNT number := 0;
cursor MYCOUNT is
select count(*), sysdate
from seat_assignment
where s_add_on_cd != 'N/A';
begin
open MYCOUNT;
fetch MYCOUNT into V_COUNT;
close MYCOUNT;
insert into stats (logged_on, system_date)
values(V_COUNT, sysdate);
end;
-Matt Received on Sun May 14 2000 - 00:00:00 CEST