Firebird数据库动态SQL错误-104排查：WHERE子句子查询问题

Let's break down your issue and fix that frustrating -104 error first, then make sure you get the latest certificate records you need.

Why You're Seeing the Error #

The Dynamic SQL Error -104; Token Unknown almost always points to a syntax quirk that Firebird's parser isn't happy with. Looking at your query, the likely culprit is the AS keyword used for the table alias in your subquery. While modern Firebird versions support AS for table aliases, older versions (pre-2.5 maybe?) can be finicky about it. The error position you mentioned (line 11, column 30) lines up right where AS Z is in your subquery.

Quick Fix for the Syntax Error #

Try removing the AS keyword when aliasing the CERTS table in your subquery. Firebird doesn't require AS for table aliases, so this should resolve the token unknown error:

SELECT 
    EVENTS.DONE_BY_FNAME, 
    CERTS.CERT_NUM, 
    CERTS.CERT_DATE, 
    CERTS.GAGE_SN, 
    EVENTS.VENDOR 
FROM EVENTS 
INNER JOIN CERTS ON CERTS.EVENT_NUM = EVENTS.EVENT_NUM 
WHERE CERTS.CERT_DATE = (
    SELECT MAX(Z.CERT_DATE) 
    FROM CERTS Z  -- Removed AS here
    WHERE Z.EVENT_NUM = CERTS.EVENT_NUM 
)

A More Reliable Approach (Using Window Functions) #

If you're running Firebird 3.0 or newer, using window functions is a cleaner, more efficient way to get the latest CERT_DATE per EVENT_NUM—and it avoids potential subquery syntax pitfalls. Here's how to rewrite your query with ROW_NUMBER():

SELECT 
    EVENTS.DONE_BY_FNAME, 
    CERTS.CERT_NUM, 
    CERTS.CERT_DATE, 
    CERTS.GAGE_SN, 
    EVENTS.VENDOR
FROM (
    SELECT 
        CERT_NUM, 
        CERT_DATE, 
        GAGE_SN, 
        EVENT_NUM,
        -- Assign row number, ordered by newest CERT_DATE first per EVENT_NUM
        ROW_NUMBER() OVER (PARTITION BY EVENT_NUM ORDER BY CERT_DATE DESC) AS rn
    FROM CERTS
) AS CERTS
INNER JOIN EVENTS ON CERTS.EVENT_NUM = EVENTS.EVENT_NUM
-- Pick only the newest record per EVENT_NUM
WHERE CERTS.rn = 1

This query will explicitly rank each certificate in a group by EVENT_NUM (newest first) and then filter to keep only the top-ranked record—exactly the result you're looking for.

Checking Timestamp Comparisons #

If you still run into issues with the CERT_DATE comparison (even after fixing syntax), double-check if your TIMESTAMP values have fractional seconds that are throwing off the equality check. For example, if your stored CERT_DATE is 2019-01-14 00:00:00.123 but you're comparing to 2019-01-14 00:00:00.0, the equality will fail. In that case, cast both sides to DATE to compare just the date part:

WHERE CAST(CERTS.CERT_DATE AS DATE) = (
    SELECT CAST(MAX(Z.CERT_DATE) AS DATE) 
    FROM CERTS Z 
    WHERE Z.EVENT_NUM = CERTS.EVENT_NUM 
)

内容的提问来源于stack exchange，提问作者M. Coull