SC9 - SQL-statement misinterpreted

Scriptcase 9 Bugs
#1

Hey,
I have the following sql:

(SELECT DISTINCT
fkStudierender AS Studi,
IF(KursBestanden(leistungen.fkKurs,fkStudierender) = 0, ‘NB’, ‘BE’) AS BENB,
‘Kurs’ AS Art
FROM
ergebnisse, leistungen
WHERE
leistungen.idLeistung = ergebnisse.fkLeistung
AND
leistungen.fkKurs = ‘[idKurs]’
)
UNION
(SELECT DISTINCT
fkStudierender AS Studi,
IF(PraktikumBestandenBisInklKurs(leistungen.fkKurs,fkStudierender) = 0, ‘NB’, ‘BE’) AS BENB,
‘Praktikum’ AS Art
FROM
ergebnisse, leistungen
WHERE
leistungen.idLeistung = ergebnisse.fkLeistung
AND
leistungen.fkKurs =’[idKurs]’
)

in a grid, where I use a diagram to show summary with count of dataset for BENB and Art on the x-axis.

It works in SC8, but in SC9 I get a long error message: [TABLE=“class: scErrorTable, align: center, cellpadding: 0, cellspacing: 0, width: 320, height: 30”]
[TR]
[TD=“class: scErrorTitle, align: left”]Fehler[/TD]
[/TR]
[TR]
[TD=“class: scErrorMessage, align: center”]Fehler beim Datenbankzugriff:
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 ’ BENB as a_cmp_1,Art as a_cmp_2 from ((SELECT DISTINCT fkStudierender AS Studi,’ at line 1
select count(*),a_cmp_1 from (select , BENB as a_cmp_1,Art as a_cmp_2 from ((SELECT DISTINCT fkStudierender AS Studi, IF(KursBestanden(leistungen.fkKurs,fkStudierender) = 0, ‘NB’, ‘BE’) AS BENB, ‘Kurs’ AS Art FROM ergebnisse, leistungen WHERE leistungen.idLeistung = ergebnisse.fkLeistung AND leistungen.fkKurs = ‘105’ ) UNION (SELECT DISTINCT fkStudierender AS Studi, IF(PraktikumBestandenBisInklKurs(leistungen.fkKurs,fkStudierender) = 0, ‘NB’, ‘BE’) AS BENB, ‘Praktikum’ AS Art FROM ergebnisse, leistungen WHERE leistungen.idLeistung = ergebnisse.fkLeistung AND leistungen.fkKurs =‘105’ ) ) sc_sel_esp1 ) SC_sel1 INNER JOIN (select BENB as b_cmp_1,Art as b_cmp_2 from ((SELECT DISTINCT fkStudierender AS Studi, IF(KursBestanden(leistungen.fkKurs,fkStudierender) = 0, ‘NB’, ‘BE’) AS BENB, ‘Kurs’ AS Art FROM ergebnisse, leistungen WHERE leistungen.idLeistung = ergebnisse.fkLeistung AND leistungen.fkKurs = ‘105’ ) UNION (SELECT DISTINCT fkStudierender AS Studi, IF(PraktikumBestandenBisInklKurs(leistungen.fkKurs,fkStudierender) = 0, ‘NB’, ‘BE’) AS BENB, ‘Praktikum’ AS Art FROM ergebnisse, leistungen WHERE leistungen.idLeistung = ergebnisse.fkLeistung AND leistungen.fkKurs =‘105’ ) ) sc_sel_esp2 group by BENB,Art) SC_sel2 ON SC_sel1.a_cmp_1 = SC_sel2.b_cmp_1 and SC_sel1.a_cmp_2 = SC_sel2.b_cmp_2 group by a_cmp_1 order by a_cmp_1[/TD]
[/TR]
[/TABLE]

It is still the same mysql-DB and the sql runs fine in mysql-tool.
I think SC9 does some wrong work on the sql…

#2

Complex sql has always been a struggle as SC has it’s own sql parser which goes wrong with unions and other more complex sql. Best solution? Use views.

#3

No. This works in SC8 but not SC9. This is a significant defect which Netmake should be made aware of. Sure, views could work, but expecting paying SC customers to create views as a work around, when this worked fine before, is not right.

#4

Yes, I read about using views. But in this case it is not the solution I think:
the graphic is part of a dashboard, depending on the [idKurs]. Views could not get an parameter. To build the view for all Kurse will be very expensive because the functions to be evaluated are time-consuming and there is no way without them.
The main argument is: in SC8 is works, but not so in SC9.

So Netmake is in duty to solve this, so that parer in sc9 works same as in sc8.

#5

Guys, I don’t say that SC should not fix bugs. But where possible I try to show work-arounds.

#6

Oh course and it is very pleasing that you are so helpful in so many cases. Thanks for that.

Nevertheless, I can not solve the problem without NetMake to fix the mistake

#7

Just a strange thing to try:
SELECT DISTINCT
fkStudierender AS Studi,IF(KursBestanden(leistungen.fkKurs,fkStudierender) = 0, ‘NB’, ‘BE’) AS BENB, ‘Kurs’ AS Art
FROM
ergebnisse, leistungen
WHERE
leistungen.idLeistung = ergebnisse.fkLeistung
AND
leistungen.fkKurs = ‘[idKurs]’

UNION

SELECT DISTINCT
fkStudierender AS Studi, IF(PraktikumBestandenBisInklKurs(leistungen.fkKurs ,fkStudierender) = 0, ‘NB’, ‘BE’) AS BENB,‘Praktikum’ AS Art
FROM
ergebnisse, leistungen
WHERE
leistungen.idLeistung = ergebnisse.fkLeistung AND leistungen.fkKurs =’[idKurs]’

So actually without the extra ( ) around the selects… As Albert said the parser isnt the best…

#8

Thanks f?r the idea. I have of course tried this my self, and even other versions like select * from ((select…)Union(select…)) - same problem. I think the SC-parser just gets it wrong, though the sc8-version could manage.