0

I have this query that is executed in my php code:

SELECT ((SUM(CASE WHEN I.InCompliance=1 AND FI.Status='C' THEN 1 ELSE 0 END)*100) / (SUM(CASE WHEN FI.Status='C' THEN 1 ELSE 0 END))) as result 
FROM tbl_Inspection I,tbl_Facility F,tbl_FacilityInspection FI 
WHERE I.InspectionDate >= '2013-2-01' 
AND I.InspectionDate < '2014-3-01' 
AND I.belongsToFacility=F.FacilityID 
AND I.FacInspId=FI.FacInspId 
AND F.DistrictID=1

it executes just fine for me in mssql management studio but I get this error

Warning: odbc_exec(): SQL error: [unixODBC][FreeTDS][SQL Server]The cursor was not declared., SQL state 37000 in SQLExecDirect in /srv/www/htdocs/capsdev/includes/phpdbform_db_odbc.php on line 104 SELECT ((SUM(CASE WHEN I.InCompliance=1 AND FI.Status='C' THEN 1 ELSE 0 END)*100) / (SUM(CASE WHEN FI.Status='C' THEN 1 ELSE 0 END))) as result FROM tbl_Inspection I,tbl_Facility F,tbl_FacilityInspection FI WHERE I.InspectionDate >= '2013-2-01' AND I.InspectionDate < '2014-3-01' AND I.belongsToFacility=F.FacilityID AND I.FacInspId=FI.FacInspId AND F.DistrictID=1 Warning: odbc_fetch_array() expects parameter 1 to be resource, boolean given in /srv/www/htdocs/capsdev/includes/phpdbform_db_odbc.php on line 114

Any help would be much appreciated.

Thank you,

Daniel

dsauerbrun
  • 13
  • 1
  • 5

1 Answers1

1

Try using explicit rather than implicit joins.

Something like:

SELECT ((SUM(CASE WHEN I.InCompliance=1 AND FI.Status='C' THEN 1 ELSE 0 END)*100) / (SUM(CASE WHEN FI.Status='C' THEN 1 ELSE 0 END))) as result 
FROM tbl_Inspection I
inner join tbl_Facility F
on I.belongsToFacility=F.FacilityID  
inner join tbl_FacilityInspection FI 
on I.FacInspId=FI.FacInspId 
WHERE I.InspectionDate >= '2013-2-01' 
AND I.InspectionDate < '2014-3-01' 
AND F.DistrictID=1
DMK
  • 2,448
  • 1
  • 24
  • 35