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  [Select][+][-]

1. Select A.DEPT_NO, A.DEPARTMENT, A.HEAD_DEPT, A.MNGR_NO, A.BUDGET, A.LOCATION, A.PHONE_NO,
2. count(B.DEPT_NO) as ChildCount,
3. case
4.   when A.Budget > 5000 then 0
5.   when A.Budget < 5000 then 2
6.   else 1 end as ImageIndex
7. From DEPARTAMENT A
8. Left Outer JOIN DEPARTAMENT B On B.HEAD_DEPT = A.DEPT_NO
9. /* where A."HEAD_DEPT" is null AND  */ /* errors are in this line */
10. where A.HEAD_DEPT is null /* this works */
11.  
12. Group By A.DEPT_NO
13. Order 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  [Select][+][-]

1. SELECT
2.     A.DEPT_NO, A.DEPARTMENT, A.HEAD_DEPT, A.MNGR_NO, A.BUDGET, A.LOCATION, A.PHONE_NO,
3.     COALESCE(B.DEPT_COUNT, 0) AS ChildCount,
4.     CASE
5.       WHEN A.Budget > 5000 THEN 0
6.       WHEN A.Budget < 5000 THEN 2
7.       ELSE 1 END AS ImageIndex
8.     FROM departament A
9.     LEFT JOIN
10.         (SELECT head_dept, COUNT(dept_no) AS DEPT_COUNT FROM departament WHERE head_dept NOT IS NULL GROUP BY head_dept) B
11.         ON B.HEAD_DEPT = A.DEPT_NO    
12.     WHERE A.HEAD_DEPT IS NULL
13.     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

[Zvoni]

LOL