Interbase SQL SELECT query using CASE

Question

I'm running the following query against a .gdb-file:

SELECT CASE USERS.USERID WHEN 1 THEN 'God' ELSE 'Somebody else' END FROM USERS;

However, running essentially the same query against a .ib-file, I get "Error at line 1 Dynamic SQL Error, SQL error code = -104, Token unknown - line 1, char 17". It seems it doesn't recognize the part CASE USERS.USERID. In IBConsole, the .ib-file is running SQL dialect 3, while the .gdb-file is running dialect 1. Does this matter? What's the difference between a .ib-file and a .gdb-file?

Answer 1

.gdb and .ib are juast file extensions. They don't affect anything.

I'm guessing the problem here is case sensitivity. Per the "delimited identifier" feature of the SQL standard, if you use double quotes when you create the table, i.e.:

CREATE TABLE "Users" (
    "UserId" INTEGER NOT NULL PRIMARY KEY, 
    /* ... */

...then the identifier is case-sensitive. Having done this, you must therefore always use (1) delimiters (double quotes) and (2) the correct case whenever you do anything with the table.

Annoying, yes, but the SQL Standard requires it. SQL Dialect 3 is much more strict about enforcing the SQL Standard, including delimited identifiers.

To get around this, don't use double quotes when you create the table unless you actually want this "feature."