0

I have in my Access .mdb

SELECT a * b AS c, c * d AS e

I must translate this clause to Firebird.

Any hint?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
IronButterfly
  • 1

1 Answers1

1

Using SELECT without a FROM clause is non-standard in the SQL language. A few implementations allow it (e.g. Microsoft Access, SQL Server, and MySQL), but others do not.

Firebird is one of those that implement standard SQL, so you must provide a FROM clause, and reference a table with at least one row, or else you get no result.

http://www.firebirdfaq.org/faq30/ says:

You can use the RDB$DATABASE which is a single-row table, and part of each database's metadata:

select current_timestamp from RDB$DATABASE;

Any other table with at least one row could be used, but the point is that RDB$DATABASE is sure to be present.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • @BillKarvin many thanks for your prompt answer. My Access query is very long and has many clauses like SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. In my Firebird Query I get the SQL error Column unknown. In Access this doesn't happen, while in Firebird it does. I just need to inform Firebird about all the columns. How to? I post another example Apples.Qty * Apples.Price AS Total, Total * 2 AS TotalDoubled. In Firebird it doesn't find Total – IronButterfly Mar 16 '22 at 19:26
  • It is standard in SQL that you cannot reference a column alias in the same select-list where it is defined, because the order of column evaluation is not guaranteed to be left-to-right. – Bill Karwin Mar 16 '22 at 19:38
  • I suppose that Access SQL is NOT standard. How to solve this problem? Do I have to use a subquery? or what else? – IronButterfly Mar 16 '22 at 19:48
  • Yes, Access breaks the SQL standard in many ways. To solve this, see https://stackoverflow.com/questions/8370114/referring-to-a-column-alias-in-a-where-clause That question is about referencing aliases in the WHERE clause, but the same answer applies to expressions in the select-list. – Bill Karwin Mar 16 '22 at 19:51