Urgent: MySQL query does not compile

Applications Grid / Reports
#1

Dear forum user,

I have an urgent question in hope for quick help:

I have a query which is perfectly running directly on the database using PhpMyAdmin interface.
Unfortunately in ScriptCase, when setting up the query for a Grid Application, I get the following error:

Error: Erro ao recuperar os campos da tabela. 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 11

Is this a bug in Scriptcase?
Can someone help me how to avoid this?

The query is the following:

SELECT
		f.Fahrgestell_Nr,
		IFNULL(mv_aktuell.Laufzeit_Von, '') Mietvertrag_aktuell_von,
		IFNULL(mv_aktuell.Laufzeit_Bis, '') Mietvertrag_aktuell_bis
	FROM
		FAHRZEUG f
		LEFT JOIN (SELECT mv1.Fahrgestell_Nr, mv1.Laufzeit_Von, mv1.Laufzeit_Bis FROM MIETVERTRAG_HISTORIE mv1 INNER JOIN (
		SELECT
  Fahrgestell_Nr,
  Max(Laufzeit_Von) Laufzeit_Von
 FROM MIETVERTRAG_HISTORIE mvhist GROUP BY Fahrgestell_Nr) mv2 ON mv1.Fahrgestell_Nr = mv2.Fahrgestell_Nr WHERE (mv1.Laufzeit_Von = mv2.Laufzeit_Von) AND ((Laufzeit_Bis IS NULL) OR (Laufzeit_Bis>=SYSDATE()))
    ) mv_aktuell ON f.Fahrgestell_Nr = mv_aktuell.Fahrgestell_Nr

Thanks a lot!

#2

Re: Urgent: MySQL query does not compile

I would look for the statement in your generated code (using grep). See if the generated code is broken by SC not knowing how to parse it.

First suspects are usually the quotes, etc. Try changing to “” instead of ‘’ in your IFNULL

Regards,
Scott.

#3

Re: Urgent: MySQL query does not compile

I’ve had several instances where code generation screwed up perfectly valid mySQL statements. My work around has been to use views stored in the database.

Cheers

#4

Re: Urgent: MySQL query does not compile

Yes, views solve a lot of problems.

Regards,
Scott.