How can I rephrase a query that uses subqueries?

Question

How can I rephrase a query that uses subqueries?

Navigation

#1 by (0 votes)

1

I want to reformulate a query of a stored procedure that uses a series of subqueries from from .

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
                FROM BTR_VARIOS va
                WHERE va.CVAR_ID = est.CNIV_NIVEL_ESTUDIOS
                ) AS NIVEL
            ,est.CEST_ESTUDIOS_ESTADO
            ,(
                SELECT var.CVAR_DESC
                FROM BTR_VARIOS var
                WHERE var.CVAR_ID = est.CEST_ESTUDIOS_ESTADO
                ) AS ESTESTUDIO
            ,est.CEST_CARRERA AS OPC_CARRERA
            ,(
                SELECT UPPER(car.cgrad_denom)
                FROM per_adm_grado car
                WHERE car.cgrad_codigo = est.CEST_CARRERA
                ) AS CARRERA
            ,est.DEST_FCH_INI AS FCHINIEST
            ,est.DEST_FCH_FIN AS FCHFINEST
            ,est.CCEN_CODIGO
            ,(
                SELECT cc.ccen_nombre
                FROM PERSONAL2.PER_CENTROS_ESTUDIOS cc
                WHERE CC.CCEN_CODIGO = EST.CCEN_CODIGO
                    AND CCEN_TIPO != 'O'
                    AND CCEN_TIPO != 'C'
                ) 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;

The problem for which I want to reformulate this query is because the following subquery ...

SELECT cc.ccen_nombre
FROM PERSONAL2.PER_CENTROS_ESTUDIOS cc  
WHERE CC.CCEN_CODIGO = EST.CCEN_CODIGO
AND CCEN_TIPO != 'O'
AND CCEN_TIPO != 'C'
) AS CENTRO

... returns more than one value and this causes me an error. For example, it returns 2 records that have the same value of cc.ccen_nombre , this causes an error, what I want is to reformulate the query in such a way that allows me to return the values and if it is possible to eliminate the subquerys .

sql tsql

asked by ASP.NEET 13.10.2016 в 02:07

source

1 answer

Error when deploying Apache Cordova - Problem with Developer Team IOS 10

score 0 · Accepted Answer

I'll give you the example with a field. This subquery:

SELECT (
        SELECT va.CVAR_DESC
        FROM BTR_VARIOS va
        WHERE va.CVAR_ID = est.CNIV_NIVEL_ESTUDIOS
        ) 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'
WHERE D.NDAT_ID = p_ndat_id
    AND D.nusr_id = p_nusr_id
ORDER BY est.CNIV_NIVEL_ESTUDIOS ASC

You can convert it to this:

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

You must do this in each field that you want to change the subquery for the union between tables.