ORA-00923错误求助：查询最新日期数据时SQL语句报错

Alright, let's figure out why you're hitting that frustrating ORA-00923 error when trying to get the latest date-specific details from your Oracle table.

First, here's the error you're seeing:

Oracle.DataAccess.Client.OracleException: ORA-00923: FROM keyword not found where expected

And here's the query you're running (note the incomplete table name in the subquery):

SELECT DISTINCT CCSMASTERLISTREVNO, CCSREVCONTENT, CCSPREPAREDREV, CCSREVEFFECTIVEDATE 
FROM CCS2_TBL_MASTERLIST 
WHERE CCSEQUIPMENTDPMT = :DPMT 
AND CCSMASTERLISTREVNO <= :REVNO 
AND CCSREVEFFECTIVEDATE = ( SELECT MAX(TO_CHAR(CCSREVEFFECTIVEDATE,'dd/MM/yyyy')) FROM CCS2_TBL_...

What's Causing the Error & Logic Issues? #

• Incomplete Table Name: Your subquery cuts off at CCS2_TBL_...—Oracle can't locate the target table for the FROM clause, which is a direct trigger for the ORA-00923 error.
• String vs. Date Comparison: You’re converting the date field to a string with TO_CHAR() before using MAX(). String sorting doesn’t align with date logic! For example, "31/12/2022" would be treated as larger than "01/01/2023" as a string, but the latter is actually the newer date. This would give you wrong results even if the syntax was fixed.

Fixed Solutions #

Option 1: Correct the Original Query

Fix the table name, compare dates directly instead of converting to strings, and add matching filters to the subquery to ensure it targets the same dataset as the main query:

SELECT DISTINCT CCSMASTERLISTREVNO, CCSREVCONTENT, CCSPREPAREDREV, CCSREVEFFECTIVEDATE 
FROM CCS2_TBL_MASTERLIST 
WHERE CCSEQUIPMENTDPMT = :DPMT 
AND CCSMASTERLISTREVNO <= :REVNO 
AND CCSREVEFFECTIVEDATE = ( 
    SELECT MAX(CCSREVEFFECTIVEDATE) 
    FROM CCS2_TBL_MASTERLIST 
    WHERE CCSEQUIPMENTDPMT = :DPMT 
    AND CCSMASTERLISTREVNO <= :REVNO
)

Option 2: Use Window Functions (Recommended)

This approach is cleaner, especially if multiple records might share the latest date. Use ROW_NUMBER() (or RANK() if you want all latest-date records) to isolate the newest entries:

SELECT CCSMASTERLISTREVNO, CCSREVCONTENT, CCSPREPAREDREV, CCSREVEFFECTIVEDATE
FROM (
    SELECT 
        CCSMASTERLISTREVNO, 
        CCSREVCONTENT, 
        CCSPREPAREDREV, 
        CCSREVEFFECTIVEDATE,
        -- Assign row numbers sorted by date descending
        ROW_NUMBER() OVER (PARTITION BY CCSEQUIPMENTDPMT ORDER BY CCSREVEFFECTIVEDATE DESC) AS rn
    FROM CCS2_TBL_MASTERLIST 
    WHERE CCSEQUIPMENTDPMT = :DPMT 
    AND CCSMASTERLISTREVNO <= :REVNO
) t
WHERE rn = 1;

If you need to keep all records that have the absolute latest date (not just one), replace ROW_NUMBER() with RANK().

内容的提问来源于stack exchange，提问作者N.I.A