SQL Alias Syntax for SubQuery in LibreOffice Base

[LOn00bie]

Hello,

could you help me with a multi-query in the database, I'm looking for alternative methods available for implementing subqueries, assuming the are more as 10 conditions to calculate values. I'm looking for something similar to do an inline alias like its done in microsoft access.

Example (Pseudocode): Select C = A*B, D = C + A From A,B

Thanks!

Regards,
LOn00bie

[FJCC]

I'm not sure exactly what you need. Here is an example of making a calculated column named Mult that is the product of the columns named Col1 in the two tables Table1 and Table2. The rows of the tables are matched using the ID column in each table.

Code: Select all

SELECT "Table1"."Col1" * "Table2"."Col1" AS "Mult" FROM "Table1", "Table2" WHERE "Table1"."ID" = "Table2"."ID"

[UnklDonald418]

To use an alias like C in more than one term of the query usually requires it to be defined in a sub-query.
The sub-query will probably need at least one more term to insure that it returns a single value. I haven't tested it but it might be something like

Code: Select all

select  M.C, (M.C + A) D
from A, B, (select  (A*B) C, A.X from A,B) as M
where M.X = A.X

[Cazer]

Just to add to what UnklDonald418 and FJCC have already said, you can definitely create calculated columns in your SQL query in LibreOffice Base. The main thing to remember is that you can't use a calculated column in the same query where it's created, unless you nest it in a sub-query.

Here's an adjusted version of UnklDonald418's example:

Code: Select all

SELECT M.C, (M.C + M.A) AS D
FROM (SELECT A, B, (A*B) AS C FROM YourTable) AS M

In this query, 'YourTable' should be replaced with the name of your table. The sub-query 'M' calculates 'C' as 'A*B', and the outer query calculates 'D' as 'C+A'.

[LOn00bie]

Hi,

thanks for your detailed answers, I would like to find something similar to Microsoft Access syntax. The question is whether it is possible to do this without Subquery ...

MS Access

Code: Select all

SELECT [A]*[B] AS C, [C]*10 AS D
FROM TEST;

Base

Code: Select all

SELECT [A]*[B] AS C, [C]*10 AS D
FROM TEST;

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -206
*Column unknown
*C
*At line 1, column 23
caused by
'isc_dsql_prepare'

or

Code: Select all

SELECT "C", "D" FROM "TEST" WHERE
( "C" = ROUND(( "A" * "B" ), 0 )) AND ( "D" = ROUND((( "C" *  ( 10 )), 0 ))

[UnklDonald418]

The question is whether it is possible to do this without Subquery ...

No.
Base is not a MSAcess clone.
Using non-standard (ISO) syntax like that locks you into MSAccess, which I am sure is part of the Microsoft marketing strategy.