I think you'll find there's no need for the order by since group by does
that already
secondly I would defintely use a view here for the one or both of the
selects, then use your union against the view. The view will behave like a
table, it will use indexes and the union against it will be quite fast.

Alan
-----Original Message-----
From: Phil Horst [mailto:phorst@...]
Sent: Saturday, 26 October 2002 3:15
To: ib-support@yahoogroups.com
Subject: [ib-support] Newbie question - combining results from two Select
Statements


I am looking for help with SQL. I keep running into roadblocks and I
suspect it has to do with the fact that I haven't yet learned to think
Firebird. My prior experience with SQL was with Sybase (SQLAnywhere).
How can I solve the following problem - in a relatively simple way?

I need to produce a report that requires data from two different subsets
of data. The first subset uses an aggregate function (Sum) to provide
totals of hours spent on active cases. The second subset is without any
aggregate functions - it simply lists all closed cases for the fiscal
year. 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.

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


1) The subset of active records:

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 '09-01-2002' and '09-30-2002'
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


2) The subset of inactive records:

SELECT Prog.ProgramDescription
, PC.FirstName as Counselor
, 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 >= '07-01-2002' and
CP.DateClosed < '09-01-2002'
order by Prog.ProgramDescription
, PC.FirstName
, C.PrimaryClientname

Yahoo! Groups Sponsor
ADVERTISEMENT




To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]