Token unknown in Firebird

Question

This query is throwing an error on firebird, how to resolve that error?

  SELECT EMPNO,SAL 
         FROM EMP E 
         where EMPNO = (SELECT MAX(DEPTNO) FROM DEPT D WHERE E.ENAME NOT like (SELECT TOP 1 ENAME 
                                                                              FROM emp E1 
                                                                              WHERE E1.EMPNO=D.DEPTNO))
         OR 1 = (SELECT MAX(DEPTNO)
                 FROM DEPT D 
                 WHERE E.ENAME like substring('NAME111',1,5))  ORDER BY EMPNO;

Here is the error.

The following error information describes the failure

ODBC Call = SQLPrepareW()

SQL State = HY000

Native error = -104(FFFFFF98)

Error Message = [ODBC Firebird Driver][Firebird]Dynamic SQL Error

SQL error code = -104

Token unknown - line 1, column 111

score 5 · Accepted Answer · edited Feb 27 '21 at 07:53

5

(edit to include alternative, more modern syntax from comments)

Instead of SELECT TOP 1 ENAME, use any of the following:

SELECT ENAME ... FETCH FIRST ROW ONLY (SQL:2008, Firebird 3+)
SELECT ENAME ... ROWS 1 (non-standard, Firebird 2+)
SELECT FIRST 1 ENAME ... (non-standard, effectively Firebird 1.5+)

Also, use

substring('NAME111' from 1 for 5)

instead of

substring('NAME111',1,5)

For example, using FIRST 1:

SELECT EMPNO,SAL
  FROM EMP E
  WHERE EMPNO = (SELECT MAX(DEPTNO)
                        FROM DEPT D
                        WHERE E.ENAME NOT LIKE (SELECT FIRST 1 ENAME
                                                  FROM emp E1
                                                  WHERE E1.EMPNO=D.DEPTN O))
        OR 1 = (SELECT MAX(DEPTNO)
                  FROM DEPT D
                  WHERE E.ENAME LIKE substring('NAME111 ' from 1 for 5))
  ORDER BY EMPNO;

edited Feb 27 '21 at 07:53

Mark Rotteveel

100,966
191
140
197

answered Jul 25 '16 at 11:17

Val Marinov

2,705
17
22

2

@kvk30: "but for SQL is non-empty" makes no sense. Firebird uses SQL as its query language. So anything you write in Firebird **is** "SQL" – Jul 25 '16 at 11:49
@kvk30 "*I used in SQL*" does not make sense. What else would you be using in Firebird? – Jul 25 '16 at 11:58
For Firebird 2.0 and up (the OP is on FB3), one should probably use [`ROWS m [TO n]`](http://firebirdsql.org/refdocs/langrefupd20-select.html#langrefupd20-select-rows) rather than `FIRST n`. – pilcrow Jul 25 '16 at 13:41
@pilcrow For Firebird 3 it would even make more sense to use the SQL standard [`FETCH FIRST ROW ONLY`](http://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/bk01ch09s06.html#rnfb30-dml-offsetfetch) – Mark Rotteveel Jul 25 '16 at 17:20

Token unknown in Firebird

1 Answers1