ORDER BY causing ORA-00907: missing right parantheses-CodePudding

Here's the SQl query. It runs fine without the ORDER BY clause in the end, but fails with the following error when the clause is present..

WITH
    -- Gets the list of missing concepts for the client domain
    MISSING_CONCEPTS_FOR_CLIENT
AS (
    SELECT DISTINCT 
        CONCEPT_NAME AS MISSING_CONCEPT_NAME,
        WORKFLOW_ID  AS MISSING_WORKFLOW_ID
    FROM 
        WF_CMN_CLIENT_CUSTOM_VALS_META_X_CLIENT 
    WHERE
    --  CODE_VALUE_LIST = -999
    --AND 
        CLIENT_MNEMONIC        = :CLIENT_MNEMONIC
    AND SOURCE_CLIENT_MNEMONIC = :SOURCE_CLIENT_MNEMONIC
)
-- Find which concepts are similar to other clients and then use those to find similar ones in the current client domain 
(  
    SELECT DISTINCT 
            CLIENT_MNEMONIC, 
            SOURCE_CLIENT_MNEMONIC,
            CODE_VALUE,
            CODE_SET,
            DISPLAY, 
            COUNT(DISPLAY) OVER (PARTITION BY DISPLAY) AS DISPLAY_COUNT, 
            MISSING_CONCEPT_NAME,
            MISSING_WORKFLOW_ID,
            SIMILARITY_SCORE,
            RECOMMENDATION_TYPE
        FROM 
        ( 
            -- Gets the list of similar concepts from other clients
            SELECT 
                CV.CLIENT_MNEMONIC, 
                CV.SOURCE_CLIENT_MNEMONIC,
                CV.CODE_VALUE,
                CV.CODE_SET,
                CV.DISPLAY, 
                MS.MISSING_CONCEPT_NAME,
                MS.MISSING_WORKFLOW_ID,
                UTL_MATCH.JARO_WINKLER_SIMILARITY(LOWER(CV.DISPLAY), LOWER(MS.MISSING_CONCEPT_NAME)) AS SIMILARITY_SCORE,
                'CROSS_CLIENT_RECOMENDATION' AS RECOMMENDATION_TYPE
            FROM 
                WF_CMN_CLIENT_CODE_VALUE_X_CLIENT CV, 
                MISSING_CONCEPTS_FOR_CLIENT MS
            WHERE 
                    CV.CLIENT_MNEMONIC        != :CLIENT_MNEMONIC
                AND CV.SOURCE_CLIENT_MNEMONIC != :SOURCE_CLIENT_MNEMONIC
        ) CROSS_CLIENT_RECOMMENDATIONS_INNER
        WHERE 
            CROSS_CLIENT_RECOMMENDATIONS_INNER.SIMILARITY_SCORE > 85
    ORDER BY CROSS_CLIENT_RECOMMENDATIONS_INNER.SIMILARITY_SCORE DESC
)

Here are the error details

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [907] [42000]: ORA-00907: missing right parenthesis

    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:544)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:451)
    at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:171)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:458)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:847)
    at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3515)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:118)
    at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:171)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:116)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4852)
    at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:213)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:37)
    at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:733)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:904)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1082)
    at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1737)
    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1692)
    at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:300)
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:329)
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.lambda$0(JDBCStatementImpl.java:131)
    at org.jkiss.dbeaver.utils.SecurityManagerUtils.wrapDriverActions(SecurityManagerUtils.java:94)
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
    ... 12 more
Caused by: Error : 907, Position : 1529, Sql = 

WITH
    -- Gets the list of missing concepts for the client domain
    MISSING_CONCEPTS_FOR_CLIENT
AS (
    SELECT DISTINCT 
        CONCEPT_NAME AS MISSING_CONCEPT_NAME,
        WORKFLOW_ID  AS MISSING_WORKFLOW_ID
    FROM 
        WF_CMN_CLIENT_CUSTOM_VALS_META_X_CLIENT 
    WHERE
    --  CODE_VALUE_LIST = -999
    --AND 
        CLIENT_MNEMONIC        = 'ihc_ut'
    AND SOURCE_CLIENT_MNEMONIC = 'prod2'
)
-- Find which concepts are similar to other clients and then use those to find similar ones in the current client domain 
(  
    SELECT DISTINCT 
            CLIENT_MNEMONIC, 
            SOURCE_CLIENT_MNEMONIC,
            CODE_VALUE,
            CODE_SET,
            DISPLAY, 
            COUNT(DISPLAY) OVER (PARTITION BY DISPLAY) AS DISPLAY_COUNT, 
            MISSING_CONCEPT_NAME,
            MISSING_WORKFLOW_ID,
            SIMILARITY_SCORE,
            RECOMMENDATION_TYPE
        FROM 
        ( 
            -- Gets the list of similar concepts from other clients
            SELECT 
                CV.CLIENT_MNEMONIC, 
                CV.SOURCE_CLIENT_MNEMONIC,
                CV.CODE_VALUE,
                CV.CODE_SET,
                CV.DISPLAY, 
                MS.MISSING_CONCEPT_NAME,
                MS.MISSING_WORKFLOW_ID,
                UTL_MATCH.JARO_WINKLER_SIMILARITY(LOWER(CV.DISPLAY), LOWER(MS.MISSING_CONCEPT_NAME)) AS SIMILARITY_SCORE,
                'CROSS_CLIENT_RECOMENDATION' AS RECOMMENDATION_TYPE
            FROM 
                WF_CMN_CLIENT_CODE_VALUE_X_CLIENT CV, 
                MISSING_CONCEPTS_FOR_CLIENT MS
            WHERE 
                    CV.CLIENT_MNEMONIC        != 'ihc_ut'
                AND CV.SOURCE_CLIENT_MNEMONIC != 'prod2'
        ) CROSS_CLIENT_RECOMMENDATIONS_INNER
        WHERE 
            CROSS_CLIENT_RECOMMENDATIONS_INNER.SIMILARITY_SCORE > 85
    ORDER BY CROSS_CLIENT_RECOMMENDATIONS_INNER.SIMILARITY_SCORE DESC
), OriginalSql = 

WITH
    -- Gets the list of missing concepts for the client domain
    MISSING_CONCEPTS_FOR_CLIENT
AS (
    SELECT DISTINCT 
        CONCEPT_NAME AS MISSING_CONCEPT_NAME,
        WORKFLOW_ID  AS MISSING_WORKFLOW_ID
    FROM 
        WF_CMN_CLIENT_CUSTOM_VALS_META_X_CLIENT 
    WHERE
    --  CODE_VALUE_LIST = -999
    --AND 
        CLIENT_MNEMONIC        = 'ihc_ut'
    AND SOURCE_CLIENT_MNEMONIC = 'prod2'
)
-- Find which concepts are similar to other clients and then use those to find similar ones in the current client domain 
(  
    SELECT DISTINCT 
            CLIENT_MNEMONIC, 
            SOURCE_CLIENT_MNEMONIC,
            CODE_VALUE,
            CODE_SET,
            DISPLAY, 
            COUNT(DISPLAY) OVER (PARTITION BY DISPLAY) AS DISPLAY_COUNT, 
            MISSING_CONCEPT_NAME,
            MISSING_WORKFLOW_ID,
            SIMILARITY_SCORE,
            RECOMMENDATION_TYPE
        FROM 
        ( 
            -- Gets the list of similar concepts from other clients
            SELECT 
                CV.CLIENT_MNEMONIC, 
                CV.SOURCE_CLIENT_MNEMONIC,
                CV.CODE_VALUE,
                CV.CODE_SET,
                CV.DISPLAY, 
                MS.MISSING_CONCEPT_NAME,
                MS.MISSING_WORKFLOW_ID,
                UTL_MATCH.JARO_WINKLER_SIMILARITY(LOWER(CV.DISPLAY), LOWER(MS.MISSING_CONCEPT_NAME)) AS SIMILARITY_SCORE,
                'CROSS_CLIENT_RECOMENDATION' AS RECOMMENDATION_TYPE
            FROM 
                WF_CMN_CLIENT_CODE_VALUE_X_CLIENT CV, 
                MISSING_CONCEPTS_FOR_CLIENT MS
            WHERE 
                    CV.CLIENT_MNEMONIC        != 'ihc_ut'
                AND CV.SOURCE_CLIENT_MNEMONIC != 'prod2'
        ) CROSS_CLIENT_RECOMMENDATIONS_INNER
        WHERE 
            CROSS_CLIENT_RECOMMENDATIONS_INNER.SIMILARITY_SCORE > 85
    ORDER BY CROSS_CLIENT_RECOMMENDATIONS.SIMILARITY_SCORE DESC
), Error Msg = ORA-00907: missing right parenthesis

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
    ... 29 more

EDIT

The ORDER BY clause now uses the inner table name but the issue persists.

CodePudding user response：

The parentheses are making your main query act as a subquery, essentially as if you had:

WITH ...
)
SELECT * FROM -- not actually in your query
(  
    SELECT DISTINCT 
    ...
    ORDER BY CROSS_CLIENT_RECOMMENDATIONS_INNER.SIMILARITY_SCORE DESC
)

which is more obviously problematic; you can't order a subquery and it wouldn't really make sense to do so in most case. (You usually can't, anyway - there are exceptions, particularly for pagination where you order an inline view and then filter on rownum.)

You don't need the parentheses, so just remove them:

WITH ...
)
SELECT DISTINCT 
...
ORDER BY CROSS_CLIENT_RECOMMENDATIONS_INNER.SIMILARITY_SCORE DESC