Forum > Packages and Libraries
Why MySQL manualy execute but not in app
eldonfsr:
Hi tried to run sql on mysql it run directly and design time but when i run app and execute sql send me error some body with more experience than me can help me please.....
dseligo:
You have two errors in 'where' clause:
1. double quotes around HEAD_DEPT
2. 'and' without condition
--- Code: MySQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---Select A.DEPT_NO, A.DEPARTMENT, A.HEAD_DEPT, A.MNGR_NO, A.BUDGET, A.LOCATION, A.PHONE_NO,count(B.DEPT_NO) as ChildCount,case when A.Budget > 5000 then 0 when A.Budget < 5000 then 2 else 1 end as ImageIndexFrom DEPARTAMENT ALeft Outer JOIN DEPARTAMENT B On B.HEAD_DEPT = A.DEPT_NO/* where A."HEAD_DEPT" is null AND */ /* errors are in this line */where A.HEAD_DEPT is null /* this works */ Group By A.DEPT_NOOrder by Department
Zvoni:
What dseligo said.
Note: MySQL uses the Backtick ` to escape Fieldnames (instead of double-/singlequote like other DBMS).
And it's good practice to ALWAYS escape Field- and Tablenames.
btw: I'd be surprised if even with dseligo's corrections this SQL would work.
I see a GROUP BY a single Field, but the Aggregation is only on ChildCount, so MySQL should actually complain the "other" non-aggregated Fields are missing.
That said: I'd probably use a Sub-Select (since it's MySQL5.7) for "B" and be done with it
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT A.DEPT_NO, A.DEPARTMENT, A.HEAD_DEPT, A.MNGR_NO, A.BUDGET, A.LOCATION, A.PHONE_NO, COALESCE(B.DEPT_COUNT, 0) AS ChildCount, CASE WHEN A.Budget > 5000 THEN 0 WHEN A.Budget < 5000 THEN 2 ELSE 1 END AS ImageIndex FROM departament A LEFT JOIN (SELECT head_dept, COUNT(dept_no) AS DEPT_COUNT FROM departament WHERE head_dept NOT IS NULL GROUP BY head_dept) B ON B.HEAD_DEPT = A.DEPT_NO WHERE A.HEAD_DEPT IS NULL ORDER BY A.Department
I've added the COALESCE since it's a LEFT JOIN.
If you count something, there is no NULL, but 0
eldonfsr:
Thanks Desi let me try and i let you know is working or not...
thanks so much
eldonfsr:
After playing with mysql still not understand what going on you can see at image attached how give me different results...
first on is runing client mysqlfront.....
second is runing design mode connect and run sql
thirth one is running application don't send me error but not result...
sql statement
Select A.DEPT_NO, A.DEPARTMENT, A.HEAD_DEPT, A.MNGR_NO, A.BUDGET, A.LOCATION, A.PHONE_NO, count(B.DEPT_NO) as ChildCount,
case when A.Budget > 5000 then 0 when A.Budget < 5000 then 2 else 1 end as ImageIndex
From DEPARTAMENT A Left Outer JOIN DEPARTAMENT AS B On B.HEAD_DEPT = A.DEPT_NO
where A.HEAD_DEPT is null AND A.DEPT_NO <> ' '
Group By A.DEPT_NO
Order by Department
Navigation
[0] Message Index
[#] Next page