Hi Phil,

Phil Horst wrote:
<snip>

> I would like to combine these two subsets into one cursor or
> table.
>
> So far I have considered the following approaches - without success.
>
> 1) I can't use UNION to combine the subsets because it seems to be
> illegal to include aggregate functions in a UNION statement.

don't know that... but see below

> 2) I then considered creating a work table. It seems that INSERT ...
> Select ... won't work on the subset with the aggregate function because
> it seems that the ORDER By clause is not acceptable in an Insert
> statement.
>
> Am I missing something in the above attempts? Or can someone tell me
> what might work? Below are the select statements that I am using to
> prepare the different subsets.
>
> Thanks for any help offered
> Phil Horst
> Tabor Community Services

I'm not sure if your problem can be solved with UNION, too little experience
with that :-( - BUT:

There is one approach that can solve just about any problem with Selects:
use a Stored Procedure. It can mimic a "virtual" table which you can
Select From just like it where an ordinary table. (the downside to this approach
is that you should better know in advance how the result set should be ordered,
since Select from a StoredProc can not use indexes)

you could try something like:
/* note all the data types are guessed, fill in the right ones */

create procedure spPhil
(start_active date, end_active date,
start_inactive date, end_inactive date)
returns
(ProgramDescription char(32),
FirstName char(32),
ProgramID integer,
PrimaryClientName char(32),
CaseID integer,
Nettime double precision)
as
begin
FOR select PR.ProgramDescription
, PC.FirstName
, CL.ProgramID
, C.PrimaryClientName
, CL.CaseID
, sum(CL.nettime)
from caselog CL
left outer join HBprogram PR on CL.ProgramID = PR.ProgramID
left outer join CaseManager CM on CL.CaseManagerID =
CM.CaseManagerID
left outer join Person PC on CM.PersonID = PC.PersonID
left outer join Case C on CL.CaseID = C.CaseID
where
logdate between :start_active and :end_active
and nettime > 0
group by
PR.ProgramDescription
,CL.ProgramID
,PC.FirstName
,C.PrimaryClientName
,CL.CaseID
order by
PR.ProgramDescription
,CL.ProgramID
,PC.FirstName
,C.PrimaryClientName
,CL.CaseID
INTO
:ProgramDescription,
:FirstName,
:ProgramID,
:PrimaryClientName,
:CaseID,
:Nettime
DO
Suspend; /* this will return all the records for this select one by one */

/* as long as there's no EXIT you can just go on, thus "simulating" a UNION */

nettime = 0; /* it won't change any more so you can pre-set it */

FOR SELECT Prog.ProgramDescription
, PC.FirstName
, CP.ProgramID
, C.PrimaryClientName
, C.CaseID
, 0 as NetTime
FROM
caseProgram CP
left outer join HBProgram Prog on Prog.ProgramID = CP.ProgramID
left outer join CaseManager CM on CP.CaseManagerID =
CM.CaseManagerID
left outer join Person PC on CM.PersonID = PC.PersonID
left outer join Case C on CP.CaseID = C.CaseID
where
CP.DateClosed >= :start_inactive and
CP.DateClosed < :end_inactive
order by Prog.ProgramDescription
, PC.FirstName
, C.PrimaryClientname
INTO
:ProgramDescription,
:FirstName,
:ProgramID,
:PrimaryClientName,
:CaseID,
:Nettime
DO
Suspend; /* again return row by row */

exit;
end

and then (after you GRANTed the right to execute the sp to the user(s) in
question) you can do:

select * from spPhil('09-01-2002','09-30-2002','07-01-2002','09-01-2002')

SPs are very powerful things if you use them right. Just have a go at it!
(if you have any trouble with the SP - it's untested - just keep asking :-)

regards & hope this helps,
fingerman
--
-------------------------------------------------------------------------
when parsers parse, and compilers compile, then why don't objects object?

fingerbirdy - fingerman's door to Firebird
http://www.fingerbird.de