Re: Help... How Do I?

From: Matt B. <mcb_at_fightspam.sd.znet.com>
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

Original text of this message