SC8 generates different result from sql-query than mysql-client

Applications Grid / Reports
#1

Hello,
I am more than astonished. Here is my sql-query I put into an grid-appl.

SELECT
LeistungsartKurz AS Leistung,
SUM(IF(LeistungsartImKursBestanden([idKurs],fkStudierender,idLeistungsart)=0,0,1)) AS BE,
SUM(IF(LeistungsartImKursBestanden([idKurs],fkStudierender,idLeistungsart)=0,1,0)) AS NB
FROM
leistungen,
leistungsarten,
ergebnisse
WHERE
leistungen.fkKurs = [idKurs]
AND
leistungen.idLeistung = ergebnisse.fkLeistung
AND
leistungsarten.idLeistungsart = leistungen.fkLeistungsart
GROUP BY Leistung

the [idKurs] is handed over from the calling application.

The astonishing thing is the following:
with one special [idKurs]-entry the result shown in the grid is correct and identical to the one I get from any mysql-client.
BUT: with any other value for the [idKurs]-variable the values are wrong an different to the one from the mysql-client.
I even changed the variable [idKurs] to a fixed value of say 105. But still the result is wrong.

I wanted to use this to generate a column-plot - but I found the same behavior when I put similar sql to a chart or a grid and used the group-function in there.

Has anyone any idea?
I cost my the whole day today and finally I achieved nothing :frowning:

Thanks in advance…
Holger

#2

did you enabled debug and checked if SQL is correct?

#3

Thanks for this idea. I thought it could not be wrong - but I was. Here is what I found:

[SIZE=12px]SELECT leistungsarten.Leistungsart as leistungsarten_leistungsart, SUM(IF(LeistungsartImKursBestanden(86,fkStudierender,idLeistungsart)=0,0,1)) as be, SUM(IF(LeistungsartImKursBestanden(86,fkStudierender,idLeistungsart)=0,1,0)) as nb from leistungen, leistungsarten, ergebnisse where leistungen.fkKurs = 105 AND leistungen.idLeistung = ergebnisse.fkLeistung AND leistungsarten.idLeistungsart = leistungen.fkLeistungsart group by Leistungsart LIMIT 0,12
[/SIZE]
It means: the parameter [idKurs] is in the sql-statement three times, but only the one in the where-clause has the right value. The other is an old-value from the dataset I have started and tested the development with.
Obviously that is a bug in SC I assume.
When I changed the 105-value to 97 or something else, the two 86 entries stay the same, so the sql I have inserted is not fully used.

How to prevent this?

#4

Well, I have found my solution:

SELECT
Leistungsart,
SUM(IF(LeistungsartImKursBestanden(leistungen.fkKurs,fkStudierender,idLeistungsart)=0,0,1)) AS BE, SUM(IF(LeistungsartImKursBestanden(leistungen.fkKurs,fkStudierender,idLeistungsart)=0,1,0)) AS NB
FROM
leistungen,
leistungsarten,
ergebnisse
WHERE
leistungen.fkKurs = [idKurs]
AND
leistungen.idLeistung = ergebnisse.fkLeistung
AND
leistungsarten.idLeistungsart = leistungen.fkLeistungsart
GROUP BY Leistungsart

As a result: obviously SC only uses the parameter in the where-clause and not in the select.
So I have learned something about haw SC works - I admit unexpected behavior…

Thanks for the idea with debug. I will not forget to check this in future time, because who knows what SC made out of my statements…
Holger:)