[SOLVED] Query fail Oracle but working in SQL Server

Query fail Oracle but working in SQL Server

I have a query that runs perfectly n SQL server but in Oracle, I get the following error:

ORA-00923: FROM keyword not found where expected

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" = 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", 
                             "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

I already tried to check syntax follow some articles but I'm not able to solve it. Added " around de the aliases, tried to replace attribute names in the subquery not to match the parent but nothing worked. Hope you could help me with this since I went to the point I have no clue about what to try next! Cheers

Solution

Using the following web: https://rextester.com/l/oracle_online_compiler I tryed your SQL and got the same error as you. I then started simplifing it up to:

SELECT 1 AS RowNum FROM blah

and still getting the same error. I think RowNum is a reserved word in Oracle, you can not use it as a field alias.