> 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
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
