UNION?

kurtbilde · Post by **kurtbilde** » Thu 02 Apr 2009 07:07

Hi,

This makes a "List out of bounce" in net mode:

quFagkatalog.SQL.clear;
quFagkatalog.sql.add('SELECT FB.FAGBESKRIVELSE_ID, FE.SPROG_ID, FB.U_AKTIVITET ' +
'FROM FAGBESKRIVELSE FB, FBELEMENT FE ' +
'WHERE FE.FAGBESKRIVELSE_ID = FB.FAGBESKRIVELSE_ID AND FB.WEB_STATUS_ID = :galdende AND FB.FAGUDBUDKATALOG = :fagkatalogOk AND FB.PERIODESTART_ID = :periode ' +
'UNION ' +
'SELECT FB.FAGBESKRIVELSE_ID, FE.SPROG_ID, FB.U_AKTIVITET ' +
'FROM FAGBESKRIVELSE FB, FBELEMENT FE, FKELEMENT FK ' +
'WHERE FE.FAGBESKRIVELSE_ID = FB.FAGBESKRIVELSE_ID AND FB.WEB_STATUS_ID = :galdende AND FB.PERIODESTART_ID = :periode AND ' +
'FB.FAGBESKRIVELSE_ID = FK.FAGBESKRIVELSE_ID AND FK.STUDIENAEVN_ID = :studienaevn '+
'ORDER BY 2, 3 ');
quFagkatalog.params[0].value := 4;
quFagkatalog.params[1].value := 1;
quFagkatalog.params[2].value := Periode_id;
quFagkatalog.params[3].value := 4;
quFagkatalog.params[4].value := Periode_id;
quFagkatalog.params[5].value := Studienaevn_id;

Using Delphi 2007 and lastest ODAC 6.70.0.45

-Kurt

Plash · Post by **Plash** » Thu 02 Apr 2009 13:36

Please send to odac*devart*com a complete sample that demonstrates the problem, including the script for creating database objects.

kurtbilde · Post by **kurtbilde** » Thu 02 Apr 2009 17:44

Hi,

This is rather dificult as this is a large application...

Normaly Oracle handles Order by statement with columnumbers as the colums can have different names.

Any thing I can do to help you differnetly, in order to solve the issue?

-Kurt

Plash · Post by **Plash** » Mon 06 Apr 2009 08:28

Maybe you can simplify your SQL statement for reproducing the error. Try to remove some parts of your statement.

kurtbilde · Post by **kurtbilde** » Wed 15 Apr 2009 19:10

Hi,

Found the error! It is the parameters that isn't allowed to be named the same:

quFagkatalog.sql.add('SELECT FB.FAGBESKRIVELSE_ID, FE.SPROG_ID, FB.U_AKTIVITET ' +
'FROM FAGBESKRIVELSE FB, FBELEMENT FE ' +
'WHERE FE.FAGBESKRIVELSE_ID = FB.FAGBESKRIVELSE_ID AND FB.WEB_STATUS_ID = :galdende AND FB.FAGUDBUDKATALOG = :fagkatalogOk AND FB.PERIODESTART_ID = :periode ' +
'UNION ' +
'SELECT FB.FAGBESKRIVELSE_ID, FE.SPROG_ID, FB.U_AKTIVITET ' +
'FROM FAGBESKRIVELSE FB, FBELEMENT FE, FKELEMENT FK ' +
'WHERE FE.FAGBESKRIVELSE_ID = FB.FAGBESKRIVELSE_ID AND FB.WEB_STATUS_ID = :galdende AND FB.PERIODESTART_ID = :periode AND ' +
'FB.FAGBESKRIVELSE_ID = FK.FAGBESKRIVELSE_ID AND FK.STUDIENAEVN_ID = :studienaevn '+
'ORDER BY 2, 3 ');

This doesn't works!!!!!

If you then renames the last two of them it works:
quFagkatalog.sql.add('SELECT FB.FAGBESKRIVELSE_ID, FE.SPROG_ID, FB.U_AKTIVITET ' +
'FROM FAGBESKRIVELSE FB, FBELEMENT FE ' +
'WHERE FE.FAGBESKRIVELSE_ID = FB.FAGBESKRIVELSE_ID AND FB.WEB_STATUS_ID = :galdende AND FB.FAGUDBUDKATALOG = :fagkatalogOk AND FB.PERIODESTART_ID = :periode ' +
'UNION ' +
'SELECT FB.FAGBESKRIVELSE_ID, FE.SPROG_ID, FB.U_AKTIVITET ' +
'FROM FAGBESKRIVELSE FB, FBELEMENT FE, FKELEMENT FK ' +
'WHERE FE.FAGBESKRIVELSE_ID = FB.FAGBESKRIVELSE_ID AND FB.WEB_STATUS_ID = :galdende1 AND FB.PERIODESTART_ID = :periode1 AND ' +
'FB.FAGBESKRIVELSE_ID = FK.FAGBESKRIVELSE_ID AND FK.STUDIENAEVN_ID = :studienaevn '+
'ORDER BY 2, 3 ');

So never ever name parameters with the same name in the same query!

-Kurt

Devart Forums

UNION?

UNION?

UNION? - Solved! Parameters with the same name.