kvk30 kvk30 - 4 months ago 31
SQL Question

Error "Token unknown" in Firebird

I am using this SQL query through a Firebird driver.

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 are the errors.


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

Answer

Use

SELECT FIRST 1 ENAME

instead of

SELECT TOP 1 ENAME 

See SELECT FIRST SKIP

And use

substring('NAME111' from 1 for 5)

instead of

substring('NAME111',1,5)

Final Query,

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;