|
| WD18 Queries, Subqueries, Problems... Help please |
| Iniciado por guest, 12,abr. 2016 14:50 - 6 respuestas |
| |
| | | |
|
| |
| Publicado el 12,abril 2016 - 14:50 |
I can't find out what's wrong with below query.
It work ONLY in test mode with checked option:
"Run query without hQueryWithOutCorrection in test mode"
In app I get an error: "Aktivnost alias already used" (I do not use aliases)
Files are local HFSQLC/S. I checked every subquery one at time, and all worked as expected.
Does somebody have an idea?
Is it allowed to use subquery inside subquery?
(It work in some other cases)
SELECT Clan.ClanID AS ClanID, Oznaka.Oznaka AS Oznaka_Oz, Clan.Broj AS Broj, Clan.Prezime AS Prezime, Clan.Ime AS Ime, Titula.Naziv AS Naziv, Spol.Spol AS Spol, Clan.Adresa AS Adresa, Mjesto.PTT AS PTT, Mjesto.NazivMjesta AS NazivMjesta, Clan.StatusTip AS StatusTip, Clan.StatusDatumOD AS StatusDatumOD, Clan.StatusDatumDO AS StatusDatumDO, AktivnostUpit.sum_Bodovi, AktivnostUpit.sum_BodoviRegulativa, AktivnostUpit.sum_BodoviOpcenito, StatusNaDan.StatusTip AS StatusTipAktualni, StatusNaDan.Datum AS Datum FROM Clan LEFT OUTER JOIN Oznaka ON Clan.OznakaID = Oznaka.OznakaID LEFT OUTER JOIN Mjesto ON Clan.MjestoID = Mjesto.MjestoID LEFT OUTER JOIN Titula ON Clan.TitulaID = Titula.TitulaID LEFT OUTER JOIN Spol ON Clan.SpolID = Spol.SpolID LEFT OUTER JOIN ( SELECT StatusNaDan.ClanID AS ClanID, StatusNaDan.StatusTip AS StatusTip, StatusNaDan.Datum AS Datum FROM Status AS StatusNaDan INNER JOIN ( SELECT Status.ClanID AS ClanID, MAX(Status.Datum) AS maximum_Datum FROM Status WHERE Status.Datum BETWEEN {paramDatumOD} AND {paramDatumDO} GROUP BY Status.ClanID ) AS StatusMaxDate ON StatusNaDan.ClanID = StatusMaxDate.ClanID WHERE StatusNaDan.Datum = StatusMaxDate.maximum_Datum ) AS StatusNaDan ON Clan.ClanID = StatusNaDan.ClanID LEFT OUTER JOIN ( SELECT Aktivnost.ClanID, SUM(Aktivnost.Bodovi) AS sum_Bodovi, SUM(Aktivnost.BodoviRegulativa) AS sum_BodoviRegulativa, SUM(Aktivnost.BodoviOpcenito) AS sum_BodoviOpcenito FROM Aktivnost WHERE Aktivnost.Datum BETWEEN {paramDatumOD} AND {paramDatumDO} GROUP BY Aktivnost.ClanID ) AS AktivnostUpit ON Clan.ClanID = AktivnostUpit.ClanID ORDER BY Clan.Prezime, Clan.Ime |
| |
| |
| | | |
|
| | |
| |
| Publicado el 12,abril 2016 - 16:00 |
Hi Marijan,
There are alias's all over the SQL code you supplied (after the AS keyword), but I can't see from the SQL where you would get the error you describe.
I presume there is WLanguage code that executes this SQL?
Do you use HAlias in your code perhaps? |
| |
| |
| | | |
|
| | |
| |
| Publicado el 12,abril 2016 - 17:06 |
Hi Marijan,
Do you also execute your query in your app with the hQueryWithoutCorrection option?
Cheers,
Peter H. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 12,abril 2016 - 21:13 |
Hi DarrenF, Peter,
Thanks for reply,
I do not use HAlias() at all.
I did try many combinations, with and without AS keyword.
Every subquery I use here is tested before, as standalone query.
I use this query as source for one read-only table control,
so I did not use hQueryWithoutCorrection option in app.
I do not understand what actualy do this option, what is "corrected"?
and another intresting thing:
when I try to execute this SQL code from HFSQL control center,
HFSQLCC crashes. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 15,abril 2016 - 12:47 |
Hi,
Maybe the fat that you are using a sum several times
In the first select and later on
: SUM(Aktivnost.Bodovi
Regards |
| |
| |
| | | |
|
| | |
| |
| Publicado el 15,abril 2016 - 13:32 |
Hi,
I would re-factor/simplify your nested SQL so that you can prove if nesting to the level you are trying is possible. Then look at your alias's because you have the same alias in different in-line statements; e.g. ClanID. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 15,abril 2016 - 14:57 |
| |
| |
| | | |
|
| | | | |
| | |
|