Every derived table must have its own alias error

[pascale]

Hi I have just managed to set up Mariadb and created a new table and managed to over come the problem of JCDB connector! All with the help of previous posts on this site. I wanted to move/migrate my existing Database from embedded to Mariadb!
I have copied all the the tables (including views of queries) all the 97 queries, forms and reports, and pasted them into the new base all went well except the views which have not seemed to work.

Any way on to my problem,

SELECT * FROM ( SELECT "ID", "DEBIT", "CREDIT", CASE WHEN "CREDIT" IS NULL THEN 0 ELSE "CREDIT" END - CASE WHEN "DEBIT" IS NULL THEN 0 ELSE "DEBIT" END "Solde" FROM "General" LEFT JOIN ( SELECT "Debit", SUM( "MONGENDEB"."Montant" ) "DEBIT" FROM "MONGENDEB" WHERE "PID" = :AN GROUP BY "Debit" ) "A" ON "General"."ID" = "A"."Debit" LEFT JOIN ( SELECT "Credit", SUM( "MONGENCRED"."Montant" ) "CREDIT" FROM "MONGENCRED" WHERE "PID" = :AN GROUP BY "Credit" ) "B" ON "General"."ID" = "B"."Credit" ) WHERE "ID" = '764000'

This query worked perfectly well in the embedded base but not on the new mariadb,
MONGENCRED and MONGENDEB are table views of queries,

Any help will be greatly appreciated
Regards
Paul

Edit
I have corrected the two table views which are used here, and I have also tried to replace the tables with the actual queries, but I get the same message.

Edit
When I copied all the tables into the new mariadb base they all appear a if they are sub tables of a main table?
Is this normal? and if not how do I correct it.

EDIT Queries that work from the main tables (eg DATA) work with no problems, only queries that are based on other queries or there table views are having problems.

[Ratslinger]

Hello,

First I will answer your ‘…appear a if they are sub tables of a main table’ question. The so called ‘main table’ is a ‘schema’ and is a sub-set of the MariaDB (what you named as your database). This schema contains all your tables etc. You can have multiple schema in the database each separated from the other and if wanted even requiring different passwords to log in. This structure is also present in HSQLDB but with base you typically don’t see it because it always uses a default schema. You can create multiple schema in HSQLDB. Now in PostgreSQL, the container not only can contain multiple schema, but it can have multiple DB’s each of which can contain multiple schema. You should read some of the docs for MariaDB to get a better view on these and many more capabilities.

Now your main question. Moving from one DB to another usually presents SQL differences as you have probably seen. In your case SELECT * FROM ( SELECT this last select statement has no alias therefore causing the error. Even after fixing that there is still another problem - naming conventions. In this DB, “Debit” is the same as “DEBIT”. This is also confusing to someone just trying to interpret the SQL statement. Use Meaningful names such as “Total Debit” instead of “DEBIT”. Correcting the problems, here is a working statement (tested with MySQL):

SELECT `ID`, `DEBIT1`, `CREDIT1`,
       CASE WHEN `CREDIT1` IS NULL THEN 0 ELSE `CREDIT1` END 
       - CASE WHEN `DEBIT1` IS NULL THEN 0 ELSE `DEBIT1` END `Solde`
FROM `General`
  LEFT JOIN (SELECT `Debit`, SUM(`MONGENDEB`.`Montant`) `DEBIT1`
             FROM `MONGENDEB`
             WHERE `PID` = :AN
             GROUP BY `Debit`) `A` ON `General`.`ID` = `A`.`Debit`
  LEFT JOIN (SELECT `Credit`, SUM(`MONGENCRED`.`Montant`) `CREDIT1`
             FROM `MONGENCRED`
             WHERE `PID` = :AN
             GROUP BY `Credit`) `B` ON `General`.`ID` = `B`.`Credit`
WHERE `ID` = '764000'

You will probably want to change output names to better fit your needs.

Here you will notice another change - field/table names are enclosed with a back tick vs quotes.