set echo on;
Alter table "Some_Name" Add "column_Name" Integer, /* Add a new column */ Drop "xyz", /* Remove a column */ Alter column "Order" to "Sort_Order", /* changes the column name */ Alter column "Order" position 3, /* changes the column's position */ Alter column "Order" type VARCHAR(10),/* changes the column type */ Alter column "abc" type d1 /* changes the column type */ ;In the example, d1 is a domain name. As shown, you can also convert some columns from numeric to character types.
There are some limitations to modifying columns if they are used as part of a constraint, view, etc.
SET SQL DIALECT n
SET SQL DIALECT 3;In practice, this does nothing (since it is the default) ... setting the dialect to 1 will change how things work.
Firebird 1.0 used only dialect 1.
When a database is opened, the default dialect is dependent on the Firebird version used to create the table.
select * from Table1 where "Transaction_Date" = 'TODAY'
First & Skip
select FIRST num_rows SKIP start_row columns FROM table select FIRST num_rows SKIP start_row distinct columns FROM table select first 5 * from "Table_1"Notice that distinct must be after first and skip.
(I don't know if Interbase supports this)
|NULLIF(COL3, 12)||Compares two expressions, returns NULL if they are equal, otherwise returns the first expression. With strings, this is case sensitive.|
|COALESCE(COL1, COL2, 'Two NULL Columns')||Returns the first NON-NULL value|
|case .. when .. else .. end||Allows the result to be dependent on a case statement
SELECT COL1, COL2, COL3, CASE WHEN COL3 = 10 THEN 'Ten' WHEN COL3 = 34 THEN 'Thirty Four' WHEN COL3 IS NULL THEN 'Null value' ELSE COL3 END as test FROM FOO;
|iif(experssion, True_Value, False_Value)|
IIF(COL2 IS NULL, 'it is NULL', COL2)
|Immediate IF - Evaluates the expression and returns one of the 2 values
This function does not work with Firebird 1.5.3
|GEN_ID(GeneratorName, 1)||Calls the built-in generator function, returns the current value plus the second parameter and remembers the new value|
|CAST(Field1 AS VARCHAR(10))||Converts the value to a different datatype - can not be used with memo or blob data types|
|Extract(Month from Field1)||Returns the Year, Month, or Day from a Date or Timestamp field
Returns the Hour, Minute, or Second from a Time or Timestamp field
select F1, F2, F3 from Table1 where extract(month from F1) = 5
Reference - several functions listed here do NOT work with Firebird 1.5.3.
This information is based in part on "The Firebird Book" by Helen Borrie.