hanfdampf
Posts: 5
|
| Posted: 07/27/2009, 6:18 AM |
|
hello everybody
strange thing, i've been working with custom sql query for a few months, but suddenly this error keeps hassling me:
---
Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
----
When i echo the $grid->datasource->sql the output is correct ( the echoed query works fine in phpmyadmin). also in the query builder itself it works, but not in the grid on my page. i can't find no reasen for that.
here the query (if i take off the where close, it also works fine in the grid)
SELECT reg_name, pro_name, com_name, vil_name, site.site_id, Id, fence_year AS fyear, area, circon, res_name, lk1.lkup_value AS admin, termination, extended,
invb_typ, inv_biodiv.invb_id AS iId, invb_date, YEAR(inv_biodiv.invb_date) AS iyear, CAST(YEAR(inv_biodiv.invb_date)AS SIGNED)-CAST(site.fence_year AS SIGNED) AS age, inventor, inv_biodiv.creator, inv_biodiv.created, int_area AS iArea, lk2.lkup_value AS typ,
int_gps_long, int_gps_lat, ext_gps_long, ext_gps_lat,
SUM(CASE invb_loc WHEN '6' THEN CASE cat_id WHEN '1' THEN number END END) AS 't2_tr_in_cat1',
SUM(CASE invb_loc WHEN '6' THEN CASE cat_id WHEN '2' THEN number END END) AS 't2_tr_in_cat2',
SUM(CASE invb_loc WHEN '6' THEN CASE cat_id WHEN '3' THEN number END END) AS 't2_tr_in_cat3',
SUM(CASE invb_loc WHEN '6' THEN number END) AS 't2_tr_tot_in',
SUM(CASE invb_loc WHEN '7' THEN CASE cat_id WHEN '1' THEN number END END) AS 't2_tr_ou_cat1',
SUM(CASE invb_loc WHEN '7' THEN CASE cat_id WHEN '2' THEN number END END) AS 't2_tr_ou_cat2',
SUM(CASE invb_loc WHEN '7' THEN CASE cat_id WHEN '3' THEN number END END) AS 't2_tr_ou_cat3',
SUM(CASE invb_loc WHEN '7' THEN number END) AS 't2_tr_tot_ou',
COUNT(DISTINCT CASE invb_data.invb_loc WHEN '6' THEN invb_data.spec_id END) AS t2_sp_tot_in,
COUNT(DISTINCT CASE invb_data.invb_loc WHEN '6' THEN CASE invb_data.cat_id WHEN '1' THEN invb_data.spec_id END END) AS 't2_sp_in_cat1',
COUNT(DISTINCT CASE invb_data.invb_loc WHEN '6' THEN CASE invb_data.cat_id WHEN '2' THEN invb_data.spec_id END END) AS 't2_sp_in_cat2',
COUNT(DISTINCT CASE invb_data.invb_loc WHEN '6' THEN CASE invb_data.cat_id WHEN '3' THEN invb_data.spec_id END END) AS 't2_sp_in_cat3',
COUNT(DISTINCT CASE invb_data.invb_loc WHEN '7' THEN invb_data.spec_id END) AS t2_sp_tot_ou,
COUNT(DISTINCT CASE invb_data.invb_loc WHEN '7' THEN CASE invb_data.cat_id WHEN '1' THEN invb_data.spec_id END END) AS 't2_sp_ou_cat1',
COUNT(DISTINCT CASE invb_data.invb_loc WHEN '7' THEN CASE invb_data.cat_id WHEN '2' THEN invb_data.spec_id END END) AS 't2_sp_ou_cat2',
COUNT(DISTINCT CASE invb_data.invb_loc WHEN '7' THEN CASE invb_data.cat_id WHEN '3' THEN invb_data.spec_id END END) AS 't2_sp_ou_cat3'
FROM invb_data
LEFT JOIN inv_biodiv ON invb_data.invb_id = inv_biodiv.invb_id
LEFT JOIN site ON site.site_id = inv_biodiv.site_id
LEFT JOIN sit_village ON site.vil_id = sit_village.vil_id
LEFT JOIN sit_commune ON sit_village.com_id = sit_commune.com_id
LEFT JOIN sit_province ON sit_commune.pro_id = sit_province.pro_id
LEFT JOIN sit_region ON sit_province.reg_id = sit_region.reg_id
LEFT JOIN lookup AS lk1 ON site.admin_id = lk1.lkup_id
LEFT JOIN lookup AS lk2 ON inv_biodiv.invb_typ = lk2.lkup_id
LEFT JOIN sit_responsable ON site.res_id=sit_responsable.res_id
WHERE(invb_typ=0 OR 0=0)
AND (inv_biodiv.site_id= 0 OR 0=0)
AND (site.fence_year= 0 OR 0=0)
AND (YEAR(inv_biodiv.invb_date)=0 OR 0=0)
AND ((CAST(YEAR(inv_biodiv.invb_date)AS SIGNED)-CAST(site.fence_year AS SIGNED)=10000) OR 10000=10000)
AND (sit_region.reg_id = 0 OR 0=0)
AND (sit_province.pro_id = 0 OR 0=0)
AND (inv_biodiv.invb_id = 0 OR 0=0)
GROUP BY inv_biodiv.invb_id
i had already a similar problem when i added more where statements to another grid, if they were more than 8 or 9 statements, i got the above error too.
does anybody have an idea??
any help is strongly appreciated.
thx mi
_________________
----------------------------
:o)(o:
using Apache, PHP mySQL on WINxp
newBee |
