October 25, 2005 at 2:14 pm
I amdoing a query of a query in a coldfusion page and i am gettign an error when i run it. i am not sure if the problem is with the CF code or with the SQL code, but if its with the sql, perhaps you folks can help.
my master query looks like this:
<CFQUERY name="RICSV" datasource="#Application.DSN#">
SELECT AssessUniqueID, RIC, Sunnyview, Compliant75pctRule
FROM erehab_data
WHERE DischargeDate > #CreateODBCDate(Form.FromDate)# AND DischargeDate < #CreateODBCDate(Form.ToDate)#
ORDER BY RIC
</CFQUERY>
my Detail Query looks like this:
<cfquery dbtype="query" name="RicDetail">
SELECT Sunnyview,Count(*) as Discharges,
SUM(CASE WHEN compliant75pctRule = 'T' THEN 1 ELSE 0 END) AS Compliant
FROM RICSV
WHERE RIC = #RICCount.RIC#
GROUP BY Sunnyview
</cfquery>
the error is as follows:
| Query Of Queries syntax error. Encountered "SUM ( CASE. Incorrect Select List, | ||
| The error occurred in C:\Inetpub\SVIntranet\backend\erehab_report.cfm: line 102 Called from C:\Inetpub\SVIntranet\backend\index.cfm: line 149 Called from C:\Inetpub\SVIntranet\backend\erehab_report.cfm: line 102 Called from C:\Inetpub\SVIntranet\backend\index.cfm: line 149 | ||
100 : SUM(CASE WHEN compliant75pctRule = 'T' THEN 1 ELSE 0 END) AS Compliant101 : FROM RICSV102 : WHERE RIC = #RICCount.RIC#103 : GROUP BY Sunnyview104 : </cfquery> | ||
| ||
any ideas?
October 26, 2005 at 7:52 am
Is the sql case sensitive in coldfusion?
master query=Compliant75pctRule
detailed query=compliant75pctRule
October 26, 2005 at 7:58 am
nope - not case sensitive. i think i discovered its a CF thing. apparently, CASE is not allowed in a query of query in Coldfusion.
bummer!
October 26, 2005 at 9:03 am
Just do the case in the master query!
October 26, 2005 at 9:58 am
will that work though? because the detail query is part of a loop that is looping over the RICCOUNT query (a seperate query) so the detail query is executed for each record in RICCOUNT.
so if i did the SUM(CASE) statement in the master query that would be a sum of all the compliants from all RICs not just the Current RIC,right?
October 26, 2005 at 10:22 am
No, don't do the sum just ad the case to this column or the same column with the case and still do the sum in the other query:
<CFQUERY name="RICSV" datasource="#Application.DSN#">
SELECT AssessUniqueID, RIC, Sunnyview, CASE WHEN compliant75pctRule = 'T' THEN 1 ELSE 0 END
FROM erehab_data
WHERE DischargeDate > #CreateODBCDate(Form.FromDate)# AND DischargeDate < #CreateODBCDate(Form.ToDate)#
ORDER BY RIC
</CFQUERY>
<cfquery dbtype="query" name="RicDetail">
SELECT Sunnyview,Count(*) as Discharges,
SUM(compliant75pctRule) AS Compliant
FROM RICSV
WHERE RIC = #RICCount.RIC#
GROUP BY Sunnyview
</cfquery>
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply