How do I get this to work? Answer To escape an apostrophe you need to double it see manual
Tag: firebird
How to automatically detect a column with an auto-incrementing data type in Firebird?
Is it possible to find out if a Firebird table has an auto-increment column? Answer Firebird 2.5 and earlier do not have auto-increment columns, and this is usually worked around by using triggers and sequences (a.k.a. generators), as shown on this page. There is no general way to detect this style of auto-in…
Can I ask Firebird to use index (with like on strings) in left join condition on other column?
I have table structures: I can query this and SQL used indexes both on doc_date and on account: But when I am filling the selected_accounts table with data: And I am trying to use condition in left join: Then SQL is not using index on journal_entries.account data, it is using index on je.doc_date only. Can I …
Error “Column does not belong to referenced table.” in procedure with update clause
I work with IBExpert. I want to increase column STIP by IND percent in update clause in my stored procedure PR_INDEXSTIP. For example, if input parameter will be 0.25, I need to increase my field STIP by 25%. And I think that in set clause I should to write: STIP = 1.25*STIP. So, I have this code (don’t…
Avoid duplicate values using join with multiple tables in Firebird
I have 3 tables Payments PaymntID Date Amount 1 Ago.2021 500 2 Sep.2021 1200 3 Oct.2021 600 CashPayments PaymntID Amount 1 500 2 400 3 200 CreditCardPayments PaymntID Amount CreditCard 2 450 Visa 2 350 MC 3 400 Visa I need to get the PaymentID, date, amount payed in cash and amount payed in creditcard, but wi…
Summarize unique column in row
I’m new to Firebird and need your help. I have a stored procedure with following reduced output: Player Team Number Reus Ahlen 18 Lewandowski Posen 19 Reus MG 11 Reus BVB 11 Lewandowski BVB 9 Haaland BVB 9 I want to summarize the Players and transform Team&Number to a new column. The output should b…
Finding computed fields in Firebird 2.5
Computed fields are readonly. I need to find all such fields in specific table. This query solves this problem (returns info if column is computed or not): At least it looks like it solves it, but it seems that returned informations are incorrect. When RDB$UPDATE_FLAG is 0, its computed (readonly). But its 0 …
Avoid unnecessary update in “update or insert” SQL?
Here is Firebird SQL description for update or insert: https://firebirdsql.org/refdocs/langrefupd25-update-or-insert.html And my example If book already exists with id=555 and name=’Good book’ then this command executes update statement anyway and the triggering of update triggers is the only effe…
Firebird how to use IIF in ORDER BY with a subquery’s column
The following query doesn’t work, because Firebird (2.1) won’t recognize the subquery’s column. How can I rewrite it to work? Answer Use a subquery: I would also recommend using COALESCE() in the ORDER BY:
Firebird column unknown on SELECT AS
I have a table of calls and their durations: I want to select all numbers that called for over 500 seconds in total. My query uses SELECT AS to rename the SUM(…) field so I can reference it in the HAVING clause: When I run this query in Firebird SQL, I get the following error: Answer You can not use