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 0 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 0 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 0 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 0 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 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
