Well, I don't need the Sum. And also this causes the error: Cannot perform an aggregate function on an expression containing an aggregate or a subquery
Thanks - Patti > I might be wrong about what you're trying to get, but does this do it? Select > SUM(Case > ( > Select etp.etprocedureid > from eTransferProtocolLink etp > Where > etp.protocolid = 12 > ) > When then 0 > When null then 0 > Else 1 > End) as GIFT, > et.caseid > from etprocedure et > Group by > et.caseid, et.etProcedureid > > ----- Original Message ----- > From: "Patricia G. L. Hall" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Thursday, September 11, 2003 5:20 PM > Subject: Re: Repost: SQL Help > > >> > You should be able to just do a MAX() around the Gift field (if I understand your intentions correctly, you need them around some other CASE statements as well) and add group by statements as needed. >> > >> > >> I tried to implement this 3 ways. 2 gave me errors (cause they're wrong) and 1 didn't give me the results I needed. Referring back to the shorter query I started out with: >> This: >> Select >> Max >> ( >> Case >> ( >> Select count(etp.etprocedureid) >> from eTransferProtocolLink etp >> Where etp.etProcedureid = et.etProcedureid and >> etp.protocolid = 12 >> ) >> When then 0 >> When null then 0 >> Else 1 >> End >> ) >> as GIFT, >> et.caseid >> from etprocedure et >> Group by et.caseid >> Gives me this error: >> Cannot perform an aggregate function on an expression containing an aggregate or a subquery >> and so does: >> Select >> Case >> ( >> Select count( max( etp.etprocedureid) ) >> from eTransferProtocolLink etp >> Where etp.etProcedureid = et.etProcedureid and >> etp.protocolid = 12 >> ) >> When then 0 >> When null then 0 >> Else 1 >> End >> as GIFT, >> et.caseid >> from etprocedure et >> Group by et.caseid >> This gave me no error, but the results were still wrong: >> Select >> Case >> ( >> Select max(etp.etprocedureid) >> from eTransferProtocolLink etp >> Where >> etp.protocolid = 12 >> ) >> When then 0 >> When null then 0 >> Else 1 >> End as GIFT, >> et.caseid >> from etprocedure et >> Group by >> et.caseid, et.etProcedureid > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Get the mailserver that powers this list at http://www.coolfusion.com
