problem with MYSQL "HAVING" [URGENT]

Applications Grid / Reports
#1

hi,
I have a query with a WHERE and HAVING clause in MySQL Query Browser everything works correctly but the same query performed in scriptcase me the following error:

Errore durante l?accesso alla banca dati:
Unknown column ‘pag_valal’ in ‘having clause’
select count(*) from watleti as wa LEFT OUTER JOIN wmovfin as wm on wa.id_atleta=wm.id_link LEFT OUTER JOIN wcorsi_a as wh on wh.id_atleta=wa.id_atleta LEFT OUTER JOIN wcorsi_h as wc on wc.id_corso_h=wh.id_corso_h where wa.id_azienda=1 and (wm.caumov=3 or isnull(wm.caumov)) and (wc.id_stagione=1 or isnull(wc.id_stagione) ) and wa.stato=1 group by wh.id_atleta having (pag_valal < date_add(CURDATE(), INTERVAL 5 day) OR isnull(pag_valal))

there is a problem with the use of HAVING and alias?

#2

Re: problem with MYSQL “HAVING” [URGENT]

Hello Marcov,

In fact using alias on a having clause is not working properly. I will report this error to our development team.

For now what you have two options to run your application.

1 - Do not use alias, use the full field name instead. (e.g. SELECT id, SUM(total) FROM myTable GROUP BY someField having SUM(total) > 9000)

2 - Create a view for the current query, and on the SQL Statement simply call the View.

regards,
Bernhard Bernsmann

#3

Re: problem with MYSQL “HAVING” [URGENT]

You could create a VIEW in mySQL until this is fixed.

Regards,
Scott.

#4

Re: problem with MYSQL “HAVING” [URGENT]

+1 - it seems the most practical way to get around some of the code generation “quirks” of SC.

That said, if deploying to a virtual host you may find that views in mysql aren’t allowed. Gotcha!

#5

Re: problem with MYSQL “HAVING” [URGENT]

hi about this issue develop departament had fixed this?

in my case i use a very big query and make a view here is not an alternative for me cause the query can be viewed by another person i will try dont use aliases and recompile the application.

i use firebird in my project and if run the query in query builder works fine.

regards.

#6

Re: problem with MYSQL “HAVING” [URGENT]

Tambien estoy interesado en la soluci?nd e este problema, cuando podremos contar con esto?