SQL Server正常运行的Query在Oracle报ORA-00923错误求助

Let's break down the issues causing the ORA-00923: FROM keyword not found where expected error in your Oracle query, and fix them step by step:

Key Issues Identified #

1. Mismatched Column Alias in Subquery
   In your LastestPublishInfo subquery, you selected "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON16"."APPCONFIGURATIONID" but didn't assign it the alias AppConfigurationId. When you tried to reference LastestPublishInfo.AppConfigurationId in the JOIN condition, Oracle couldn't find this column, leading to parsing confusion that triggers the ORA-00923 error.

2. Logical Error in JOIN Condition
   Your LEFT JOIN for UserUpdater incorrectly uses UserCreator."ID" instead of UserUpdater."ID" in the ON clause. While this doesn't cause the ORA-00923 error, it will return incorrect results, so we'll fix it as well.

Corrected Query #

SELECT 
    MyApps.AppConfigurationId, 
    MyApps.GUID, 
    MyApps.AppName, 
    MyApps.BinaryData, 
    MyApps.Color1, 
    MyApps.UserCreatorName, 
    MyApps.CreatedAt, 
    MyApps.UserUpdaterName, 
    MyApps.UpdatedAt, 
    MyApps.UserPublisherName, 
    MyApps.LastPublishedAt, 
    MyApps.AppConfigStateId, 
    MyApps.AppConfigStateLabel 
FROM ( 
    SELECT 
        ROW_NUMBER() OVER ( 
            PARTITION BY "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON15_T266"."ID" 
            ORDER BY LastestPublishInfo."PUBLISHEDAT" DESC 
        ) AS RowNum, 
        "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON15_T266"."ID" AS AppConfigurationId, 
        "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON15_T266"."GUID", 
        "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON15_T266"."NAME" AS AppName, 
        "OSADMIN_OSDEV1"."OSUSR_4BQ_APPICON1"."BINARYDATA", 
        "OSADMIN_OSDEV1"."OSUSR_4BQ_COLORPA1"."COLOR1", 
        UserCreator."NAME" AS UserCreatorName, 
        "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON15_T266"."CREATEDAT", 
        UserUpdater."NAME" AS UserUpdaterName, 
        "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON15_T266"."UPDATEDAT", 
        LastestPublishInfo.PublisherName AS UserPublisherName, 
        LastestPublishInfo."PUBLISHEDAT" AS LastPublishedAt, 
        "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON15_T266"."APPCONFIGSTATEID", 
        "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON20"."LABEL" AS AppConfigStateLabel 
    FROM "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON15_T266" 
    JOIN "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON20" 
        ON ( 
            "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON15_T266"."APPCONFIGSTATEID" = "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON20"."ID" 
            AND "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON15_T266"."PARENTAPPCONFIGID" IS NULL 
            AND "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON15_T266"."ISACTIVE" = 1 
        ) 
    JOIN "OSADMIN_OSDEV1"."OSSYS_USER_T266" UserCreator 
        ON ( "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON15_T266"."CREATEDBY" = UserCreator."ID") 
    LEFT JOIN "OSADMIN_OSDEV1"."OSSYS_USER_T266" UserUpdater 
        ON ( "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON15_T266"."UPDATEDBY" = UserUpdater."ID") -- Fixed: Use UserUpdater.ID instead of UserCreator.ID
    LEFT JOIN "OSADMIN_OSDEV1"."OSUSR_4BQ_COLORPA1" 
        ON( "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON15_T266"."COLORPALETTEID" = "OSADMIN_OSDEV1"."OSUSR_4BQ_COLORPA1"."ID") 
    LEFT JOIN ( 
        SELECT 
            "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON16"."APPCONFIGURATIONID" AS AppConfigurationId, -- Added missing alias
            "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON16"."PUBLISHEDAT", 
            UserPublisher."NAME" AS PublisherName 
        FROM "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON16" 
        LEFT JOIN "OSADMIN_OSDEV1"."OSSYS_USER_T266" UserPublisher 
            ON ( "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON16"."PUBLISHEDBY" = UserPublisher."ID") 
        WHERE 
            "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON16"."PUBLISHEDAT" IS NOT NULL 
            AND "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON16"."ISACTIVE" = 0 
    ) LastestPublishInfo 
        ON (LastestPublishInfo.AppConfigurationId = "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON15_T266"."ID") 
    LEFT JOIN "OSADMIN_OSDEV1"."OSUSR_4BQ_APPICON1" 
        ON( "OSADMIN_OSDEV1"."OSUSR_4BQ_APPICON1"."APPCONFIGURATIONID" = "OSADMIN_OSDEV1"."OSUSR_4BQ_APPCON15_T266"."ID" ) 
) MyApps 
WHERE MyApps.RowNum = 1 
ORDER BY MyApps.UpdatedAt DESC

What Changed? #

• Added AS AppConfigurationId to the APPCONFIGURATIONID column in the LastestPublishInfo subquery, matching the alias you use in the JOIN condition to resolve the parsing error.
• Corrected the UserUpdater JOIN condition to compare UPDATEDBY with UserUpdater.ID instead of UserCreator.ID to ensure correct updater user data is retrieved.
• Cleaned up whitespace in the SELECT clause to improve readability (this doesn't affect execution, but makes the query easier to debug).

内容的提问来源于stack exchange，提问作者GNuno