[AceOmega]

I get this error when I am using a SUM.

"number of page buffers for cache required"

I tryed looking this error up to see what it means with no pages found. If any of you know what this error means it would help me.

[AceOmega]

Here is my SQL...

Select
EV.Event_Nbr ,
SE.Sub_Ev_Nbr ,
SE.Room_charge ,
SE.Start_Date ,
SE.Start_Time ,
SE.End_Time ,
EV.Event_Name ,
EV.EvType ,
EV.Client_Name ,
SE.Room_Name ,
EV.BillCatDesc ,
Sum(Event_Nbr) As EventNumberTotal
From
Events EV,
Subevent SE

Where
EV.Event_Nbr = SE.Event_Nbr
and
SE.Start_Date >= tartdate
And
SE.Start_Date <= :Enddate
And
EV.Status_Level = 1

[r123456]

The query is invalid.

Select a, b, sum(c)
from tableA
GROUP BY a, b

[AceOmega]

I forgot to post my group by. I still got the same error with the group by that I was using but I noticed that my group by was not in the same order as my select. I changed the order so that they matched and it worked. Now to see if my arithmatics on time ranges work.

Here is my final SQL....

elect
Ev.Event_Nbr ,
SE.Start_Date ,
SE.Room_Name ,
SE.Sub_Ev_Nbr ,
SE.Start_Time ,
SE.End_Time ,
EV.Event_Name ,
EV.EvType ,
EV.Client_Name ,
SE.Room_Name ,
EV.BillCatDesc ,
(SE.END_TIME - SE.START_TIME) As DailyHours,
Sum(SE.END_TIME - SE.START_TIME) As EventHours
From
Events EV
Left Join Subevent SE On
EV.Event_Nbr = SE.Event_Nbr
Where
SE.Start_Date >= tartdate
And
SE.Start_Date <= :Enddate
And
EV.Status_Level = 1

Group By
Ev.Event_Nbr ,
SE.Start_Date ,
SE.Room_Name ,
SE.Sub_Ev_Nbr ,
SE.Start_Time ,
SE.End_Time ,
EV.Event_Name ,
EV.EvType ,
EV.Client_Name ,
SE.Room_Name ,
EV.BillCatDesc

Order By
SE.START_TIME,
Ev.Event_Nbr,
SE.Room_Name


My task is to get the hours per day and the total hours per event.

[AceOmega]

I had to rewrite the SQL because it was not totaling on the groups. So I did a subselect and Now I am getting a Singleton Select error.

Here is my SQL,

Select
SE.Event_Nbr ,
SE.Start_Date ,
SE.Room_Name ,
SE.Sub_Ev_Nbr ,
SE.Event_Type ,
SE.Start_Time ,
SE.End_Time ,
EV.Event_Name ,
EV.Client_Name ,
(SE.END_TIME - SE.START_TIME) As DailyHours,
select
Sum(SE.END_TIME - SE.START_TIME)
from
SubEvent SSE
where
SSE.Event_Nbr = SE.Event_Nbr
Group By
SSE.END_TIME,
SSE.START_TIME) As EventHours

From
Events EV,
Subevent SE
Where
EV.Event_Nbr = SE.Event_Nbr
And
SE.Start_Time >= tartdate
And
SE.Start_Time <= :Enddate
And
EV.Status_Level >= 1

Order By
SE.START_TIME,
Ev.Event_Nbr,
SE.Room_Name

[r123456]

As an alternative,

select *
from
(select ta.id, sum(ta.fieldB)
from tableA ta
group by ta.id) V
INNER JOIN
tableB tb ON
tb.id = V.id
INNER JOIN
etc.

[r937]

r123456 your syntax is lovely, but how does it help?

ace, your problem is that you want to show individual rows as well as the sum of values over those individual rows

your query is getting an error because you have a GROUP BY in the subselect

a subselect in the SELECT list must be a scalar subselect, i.e. return only one value