Ошибка повторно формулируя Сторед Проседуре: Что не удается?

Продолжая трэд предыдущего вопроса я повторно формулировал моего Сторед Проседуре снимая subqueries и заменяя их из-за INNER JOIN. Потом я перешел к тому, чтобы повторно формулировать я Stored Procedure.

PROCEDURE RPT_MOSTRAR_ESTUDIOS(p_ndat_id NUMBER, p_nusr_id NUMBER, c_cursor OUT refcursor) AS

BEGIN
    OPEN c_cursor
    FOR

    SELECT DISTINCT COD_NIVEL
        ,NIVEL
        ,ESTESTUDIO
        ,OPC_CARRERA
        ,CARRERA
        ,CARRERA2
        ,FCHINIEST
        ,FCHFINEST
        ,EST_PAIS
        ,CENTRO
        ,CENTRO2
    FROM (
        SELECT est.NEST_CODIGO
            ,est.CNIV_NIVEL_ESTUDIOS AS COD_NIVEL
            ,(
                SELECT va.CVAR_DESC AS NIVEL
                FROM BTR_DAT_PER D
                INNER JOIN BTR_ESTUDIOS est ON est.NDAT_ID = D.NDAT_ID
                    AND est.NDAT_ID = p_ndat_id
                    AND EST.CEST_TIPO = 'E'
                INNER JOIN BTR_VARIOS va ON va.CVAR_ID = est.CNIV_NIVEL_ESTUDIOS
                WHERE D.NDAT_ID = p_ndat_id
                    AND D.nusr_id = p_nusr_id
                ORDER BY est.CNIV_NIVEL_ESTUDIOS ASC
                ) AS NIVEL
            ,est.CEST_ESTUDIOS_ESTADO
            ,(
                SELECT var.CVAR_DESC AS ESTESTUDIO
                FROM BTR_DAT_PER D
                INNER JOIN BTR_ESTUDIOS est ON est.NDAT_ID = D.NDAT_ID
                    AND est.NDAT_ID = p_ndat_id
                    AND EST.CEST_TIPO = 'E'
                INNER JOIN BTR_VARIOS var ON var.CVAR_ID = est.CEST_ESTUDIOS_ESTADO
                WHERE D.NDAT_ID = p_ndat_id
                    AND D.nusr_id = p_nusr_id
                ORDER BY est.CNIV_NIVEL_ESTUDIOS ASC
                ) AS ESTESTUDIO
            ,est.CEST_CARRERA AS OPC_CARRERA
            ,(
                SELECT UPPER(CA.cgrad_denom) AS CARRERA
                FROM BTR_DAT_PER D
                INNER JOIN BTR_ESTUDIOS est ON EST.NDAT_ID = D.NDAT_ID
                    AND EST.NDAT_ID = p_ndat_id
                    AND EST.CEST_TIPO = 'E'
                INNER JOIN per_adm_grado CA ON CA.cgrad_codigo = EST.CEST_CARRERA
                WHERE D.NDAT_ID = p_ndat_id
                    AND D.nusr_id = p_nusr_id
                ORDER BY EST.CNIV_NIVEL_ESTUDIOS ASC
                ) AS CARRERA
            ,est.DEST_FCH_INI AS FCHINIEST
            ,est.DEST_FCH_FIN AS FCHFINEST
            ,est.CCEN_CODIGO
            ,(
                SELECT cc.ccen_nombre AS CENTRO
                FROM BTR_DAT_PER D
                INNER JOIN BTR_ESTUDIOS est ON EST.NDAT_ID = D.NDAT_ID
                    AND est.NDAT_ID = p_ndat_id
                    AND EST.CEST_TIPO = 'E'
                INNER JOIN PERSONAL2.PER_CENTROS_ESTUDIOS cc ON CC.CCEN_CODIGO = EST.CCEN_CODIGO
                    AND CCEN_TIPO != 'O'
                WHERE D.NDAT_ID = p_ndat_id
                    AND D.nusr_id = p_nusr_id
                ORDER BY EST.CNIV_NIVEL_ESTUDIOS ASC
                ) AS CENTRO
            ,est.CPAI_CODIGO AS EST_PAIS
            ,est.CDPT_CD_DPTO
            ,EST.CPRV_CODIGO
            ,EST.CEST_CARRERA_AUX AS CARRERA2
            ,EST.CEST_CENT_AUX AS CENTRO2
            ,EST.NEST_NHORAS
        FROM BTR_DAT_PER D
        INNER JOIN BTR_ESTUDIOS est ON est.NDAT_ID = D.NDAT_ID
            AND est.NDAT_ID = p_ndat_id
            AND EST.CEST_TIPO = 'E'
        WHERE D.NDAT_ID = p_ndat_id
            AND D.nusr_id = p_nusr_id
        ORDER BY est.CNIV_NIVEL_ESTUDIOS ASC
        )
    ORDER BY COD_NIVEL;
END;

Проблема состоит в том, что он дает мне ошибку

missing right parenthesis

В линии перед первым ORDER BY

AND D.nusr_id = p_nusr_id

Тестируя твою консультацию у меня есть ошибки также :S Select Invalido

2
задан 13.04.2017, 16:00
1 ответ

ВїPara quГ© ты требуешь использования Курсора? Мне кажется, что следующий cГіdigo решает твою проблему:

ALTER PROCEDURE RPT_MOSTRAR_ESTUDIOS (p_ndat_id NUMBER, p_nusr_id NUMBER) AS

    SELECT DISTINCT COD_NIVEL
        ,NIVEL
        ,ESTESTUDIO
        ,OPC_CARRERA
        ,CARRERA
        ,CARRERA2
        ,FCHINIEST
        ,FCHFINEST
        ,EST_PAIS
        ,CENTRO
        ,CENTRO2
    FROM (
        SELECT est.NEST_CODIGO
            ,est.CNIV_NIVEL_ESTUDIOS AS COD_NIVEL
            ,(
                SELECT va.CVAR_DESC AS NIVEL
                FROM BTR_DAT_PER D
                INNER JOIN BTR_ESTUDIOS est ON est.NDAT_ID = D.NDAT_ID
                    AND est.NDAT_ID = p_ndat_id
                    AND EST.CEST_TIPO = 'E'
                INNER JOIN BTR_VARIOS va ON va.CVAR_ID = est.CNIV_NIVEL_ESTUDIOS
                WHERE D.NDAT_ID = p_ndat_id
                    AND D.nusr_id = p_nusr_id
                ORDER BY est.CNIV_NIVEL_ESTUDIOS ASC
                ) AS NIVEL
            ,est.CEST_ESTUDIOS_ESTADO
            ,(
                SELECT var.CVAR_DESC AS ESTESTUDIO
                FROM BTR_DAT_PER D
                INNER JOIN BTR_ESTUDIOS est ON est.NDAT_ID = D.NDAT_ID
                    AND est.NDAT_ID = p_ndat_id
                    AND EST.CEST_TIPO = 'E'
                INNER JOIN BTR_VARIOS var ON var.CVAR_ID = est.CEST_ESTUDIOS_ESTADO
                WHERE D.NDAT_ID = p_ndat_id
                    AND D.nusr_id = p_nusr_id
                ORDER BY est.CNIV_NIVEL_ESTUDIOS ASC
                ) AS ESTESTUDIO
            ,est.CEST_CARRERA AS OPC_CARRERA
            ,(
                SELECT UPPER(CA.cgrad_denom) AS CARRERA
                FROM BTR_DAT_PER D
                INNER JOIN BTR_ESTUDIOS est ON EST.NDAT_ID = D.NDAT_ID
                    AND EST.NDAT_ID = p_ndat_id
                    AND EST.CEST_TIPO = 'E'
                INNER JOIN per_adm_grado CA ON CA.cgrad_codigo = EST.CEST_CARRERA
                WHERE D.NDAT_ID = p_ndat_id
                    AND D.nusr_id = p_nusr_id
                ORDER BY EST.CNIV_NIVEL_ESTUDIOS ASC
                ) AS CARRERA
            ,est.DEST_FCH_INI AS FCHINIEST
            ,est.DEST_FCH_FIN AS FCHFINEST
            ,est.CCEN_CODIGO
            ,(
                SELECT cc.ccen_nombre AS CENTRO
                FROM BTR_DAT_PER D
                INNER JOIN BTR_ESTUDIOS est ON EST.NDAT_ID = D.NDAT_ID
                    AND est.NDAT_ID = p_ndat_id
                    AND EST.CEST_TIPO = 'E'
                INNER JOIN PERSONAL2.PER_CENTROS_ESTUDIOS cc ON CC.CCEN_CODIGO = EST.CCEN_CODIGO
                    AND CCEN_TIPO != 'O'
                WHERE D.NDAT_ID = p_ndat_id
                    AND D.nusr_id = p_nusr_id
                ORDER BY EST.CNIV_NIVEL_ESTUDIOS ASC
                ) AS CENTRO
            ,est.CPAI_CODIGO AS EST_PAIS
            ,est.CDPT_CD_DPTO
            ,EST.CPRV_CODIGO
            ,EST.CEST_CARRERA_AUX AS CARRERA2
            ,EST.CEST_CENT_AUX AS CENTRO2
            ,EST.NEST_NHORAS
        FROM BTR_DAT_PER D
        INNER JOIN BTR_ESTUDIOS est ON est.NDAT_ID = D.NDAT_ID
            AND est.NDAT_ID = p_ndat_id
            AND EST.CEST_TIPO = 'E'
        WHERE D.NDAT_ID = p_ndat_id
            AND D.nusr_id = p_nusr_id
        ORDER BY est.CNIV_NIVEL_ESTUDIOS ASC
        )
    ORDER BY COD_NIVEL;
GO
0
ответ дан 24.11.2019, 13:07
  • 1
    Издайте post, он дает мне проблемы эта консультация tambi и # 233; n: S – ASP.NEET 13.10.2016, 22:28
  • 2
    #191; Цюй и # 233; драйвер и qu и # 233; versi и # 243; n базы данных est и # 225; s используя? – Phi 13.10.2016, 23:03
  • 3
    bd - Оракле Версион 10.2.0.2.0 и драйвер - TOAD for Oracle – ASP.NEET 13.10.2016, 23:05

Теги

Похожие вопросы