a function inside function in a query

The problem is the sum functions inside the effectivemass function, what should i do to fix it

Code Snippet

create view missEeCuts
AS
select e.idevent
from events e, isolatedLeptons as l, isolatedLeptons as l2
where dbo.module(e.PxMiss,e.PyMiss)>=40
and dbo.effectiveMass(e.PxMiss,e.PyMiss,sum(l.px)
,sum(l2.py))<= 150.0 and l2.eventid=e.idevent and l.eventid = e.idevent and l.idap <> l2.idap;

We need you to describe the problem or error in more detail please. I imagine you are getting a syntax or Group By -type error, but need to know more info.

Thanks,
Bryan

|||now i modified to this

Code Snippet

create view missEeCuts
AS
select e.idevent
from events as e, isolatedLeptons as l, isolatedLeptons as l2
where dbo.module(e.PxMiss,e.PyMiss)>=40
and l2.eventid=e.idevent and l.eventid = e.idevent and l.idap <> l2.idap
group by e.idevent
having dbo.effectiveMass(e.PxMiss,e.PyMiss,sum(l.px)
,sum(l2.py))<= 150.0;

and its trowing me this error

Msg 8121, Level 16, State 1, Procedure missEeCuts, Line 3
Column 'events.PxMiss' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8121, Level 16, State 1, Procedure missEeCuts, Line 3
Column 'events.PyMiss' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

the thing is that i dont want to group by it by PxMiss, PyMiss
|||

I broke the code down a bit. Looking at hte logic, you might end up with a list of repeated idevent values so you may want to use a SELECT DISTINCT at the top. Still, this should take care of the GROUP BY issue

Code Snippet

create view missEeCuts
as
select
e.idevent
from events e
inner join (
select
eventid,
idap,
sum(px) as sum_px
from isolatedLeptons
group by
eventid,
idap
) l
on e.idevent=l.eventid
inner join (
select
eventid,
idap,
sum(py) as sum_py
from isolatedLeptsons
group by
eventid,
idap
) l2
on e.idevent=l2.eventid AND
l.idap <> l2.idap
where
dbo.module(e.PxMiss,e.PyMiss)>=40 AND
dbo.effectiveMass(e.PxMiss,e.PyMiss,l.sum_px),l2.sum_py))<= 150.0

|||now that one return me this error

Msg 3623, Level 16, State 1, Line 1
A domain error occurred.

the original one was this function and I need to change it to a view, returning a table with all the events that fullfil the condition

Code Snippet

create function missEeCuts
(@.idevent INT)
Returns bit
AS
BEGIN
declare @.pt31x Real;
declare @.pt31y Real;
set @.pt31x = (select sum(l.px)
from isolatedLeptons(@.idevent) as l);
set @.pt31y = (select sum(l2.py)
from isolatedLeptons(@.idevent) as l2);
return ( select distinct 1
from events e
where @.idevent= e.idevent and dbo.module(e.PxMiss,e.PyMiss)>=40
and dbo.effectiveMass(e.PxMiss,e.PyMiss,@.pt31x,@.pt31y)<= 150.0
)
END

GO

|||

There is most likely a mathematical error in there. Try running each of the subqueries independently to verify they work without errors. Also, verify the data type of the px and py fields are inline with the data types expected by the effectivemass function.

B.

|||youre right, the problem is in effectivemass