Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
7
Erster Beitrag:
vor 1 Jahr, 4 Monaten
Letzter Beitrag:
vor 1 Jahr, 4 Monaten
Beteiligte Autoren:
Marijan Tomasic, DarrenF, Allard, Peter Holemans

WD18 Queries, Subqueries, Problems... Help please

Startbeitrag von Marijan Tomasic am 12.04.2016 12: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

Antworten:

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?

von DarrenF - am 12.04.2016 14:00
Hi Marijan,

Do you also execute your query in your app with the hQueryWithoutCorrection option?

Cheers,

Peter H.

von Peter Holemans - am 12.04.2016 15:06
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.

When I use connection to HFSQL clasic files, there is no error,
only when using HFSQL C/S. (Both databases are identical, data and structure)

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 actually do this option, what is "corrected"?

and another interesting thing:
when I try to execute this SQL code from HFSQL control center,
HFSQLCC crashes.

von Marijan Tomasic - am 12.04.2016 19:13
Hi,

Maybe the fat that you are using a sum several times

In the first select and later on
: SUM(Aktivnost.Bodovi

Regards

von Allard - am 15.04.2016 10:47
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.

von DarrenF - am 15.04.2016 11:32
.

von Marijan Tomasic - am 15.04.2016 12:57
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.