SQL

Super-freak moment.
No he podido contenerme. Mi compañero estaba modificando esta consulta
y no he podido resistirme. Supongo que habrá consultas q hagan q esta a
su lado sea ridícula pero yo no habia visto cosa tan monstruosa nunca.

SELECT
'ESTADO' AS APORT,PO.FK_COD_PLAN AS PLANN,PO.FK_ANIO_COMIENZO AS ANNO,PO.EJERCICIO,PO.CI_ESTADO AS AGRUP,PO.FK_EXPEDIENTE AS EXP,
PO.FK_SUBOBRA AS SUB,
PO.CI_ESTADO AS EST,PO.CI_EUROPA AS EUR,PO.CI_AYTOS AS AYTO,
PO.CI_JUNTA AS JUNTA,PO.CI_OTRAS AS OTRAS,
P.DENOMINACION AS DENPLAN,OG.DENOMINACION AS DENOBRA,OG.FK_COD_MUNICIPIO,M.DENOMINACION AS DENMUNI,
NVL(SUM(EI.IMP_MAP),0) AS SUMA
FROM "ADMINOBRAS".PARTIDAS_OBRAS PO,"ADMINOBRAS".OBRAS_GENERAL OG,"ADMINOBRAS".PLANES P,
"ADMINOBRAS".MUNICIPIOS_PEDANIAS M,"ADMINOBRAS".EVOLUCION_INGRESOS EI
WHERE
(PO.CI_ESTADO IS NOT NULL) AND
(P.COD_PLAN=PO.FK_COD_PLAN) AND
(OG.ANIO_COMIENZO(+)=PO.FK_ANIO_COMIENZO) AND
(OG.FK_COD_PLAN(+)=PO.FK_COD_PLAN) AND
(OG.EXPEDIENTE(+)=PO.FK_EXPEDIENTE) AND
(OG.SUBOBRA(+)=PO.FK_SUBOBRA) AND
(M.COD_MUNICIPIO(+)=OG.FK_COD_MUNICIPIO) AND
(M.COD_PEDANIA(+)=OG.FK_COD_PEDANIA) AND
(EI.FK_ANIO_COMIENZO(+)=PO.FK_ANIO_COMIENZO) AND
(EI.FK_COD_PLAN(+)=PO.FK_COD_PLAN) AND
(EI.FK_EXPEDIENTE(+)=PO.FK_EXPEDIENTE) AND
(EI.FK_SUBOBRA(+)=PO.FK_SUBOBRA) AND
(TO_NUMBER(TO_CHAR(EI.F_ING_MAP,'YY'))=TO_NUMBER(TO_CHAR(PO.EJERCICIO,'00')))
:PARAMS
GROUP BY
PO.FK_COD_PLAN,PO.FK_ANIO_COMIENZO,PO.EJERCICIO,PO.CI_ESTADO,
PO.FK_EXPEDIENTE,
PO.FK_SUBOBRA,PO.CI_EUROPA,PO.CI_AYTOS,PO.CI_JUNTA,PO.CI_OTRAS,
P.DENOMINACION,OG.DENOMINACION,OG.FK_COD_MUNICIPIO,M.DENOMINACION
UNION ALL
SELECT
'EUROPA' AS APORT,PO.FK_COD_PLAN AS PLANN,PO.FK_ANIO_COMIENZO AS ANNO,PO.EJERCICIO,PO.CI_EUROPA AS AGRUP,PO.FK_EXPEDIENTE AS EXP,
PO.FK_SUBOBRA AS SUB,
PO.CI_ESTADO AS EST,PO.CI_EUROPA AS EUR,PO.CI_AYTOS AS AYTO,PO.CI_JUNTA AS JUNTA,PO.CI_OTRAS AS OTRAS,
P.DENOMINACION AS DENPLAN,OG.DENOMINACION AS DENOBRA,OG.FK_COD_MUNICIPIO,M.DENOMINACION AS DENMUNI,
NVL(SUM(EI.IMP_ADMF),0) AS SUMA
FROM "ADMINOBRAS".PARTIDAS_OBRAS PO,"ADMINOBRAS".OBRAS_GENERAL OG,"ADMINOBRAS".PLANES P,
"ADMINOBRAS".MUNICIPIOS_PEDANIAS M,"ADMINOBRAS".EVOLUCION_INGRESOS EI
WHERE
(PO.CI_EUROPA IS NOT NULL) AND
(P.COD_PLAN=PO.FK_COD_PLAN) AND
(OG.ANIO_COMIENZO(+)=PO.FK_ANIO_COMIENZO) AND
(OG.FK_COD_PLAN(+)=PO.FK_COD_PLAN) AND
(OG.EXPEDIENTE(+)=PO.FK_EXPEDIENTE) AND
(OG.SUBOBRA(+)=PO.FK_SUBOBRA) AND
(M.COD_MUNICIPIO(+)=OG.FK_COD_MUNICIPIO) AND
(M.COD_PEDANIA(+)=OG.FK_COD_PEDANIA) AND
(EI.FK_ANIO_COMIENZO(+)=PO.FK_ANIO_COMIENZO) AND
(EI.FK_COD_PLAN(+)=PO.FK_COD_PLAN) AND
(EI.FK_EXPEDIENTE(+)=PO.FK_EXPEDIENTE) AND
(EI.FK_SUBOBRA(+)=PO.FK_SUBOBRA) AND
(TO_NUMBER(TO_CHAR(EI.F_ING_ADMF,'YY'))=TO_NUMBER(TO_CHAR(PO.EJERCICIO,'00')))
:PARAMS
GROUP BY
PO.FK_COD_PLAN,PO.FK_ANIO_COMIENZO,PO.EJERCICIO,PO.CI_EUROPA,
PO.FK_EXPEDIENTE,
PO.FK_SUBOBRA,PO.CI_ESTADO,PO.CI_AYTOS,PO.CI_JUNTA,PO.CI_OTRAS,
P.DENOMINACION,OG.DENOMINACION,OG.FK_COD_MUNICIPIO,M.DENOMINACION
UNION ALL
SELECT
'AYUNTAMIENTO' AS APORT,PO.FK_COD_PLAN AS PLANN,PO.FK_ANIO_COMIENZO AS ANNO,PO.EJERCICIO,PO.CI_AYTOS AS AGRUP,PO.FK_EXPEDIENTE AS EXP,
PO.FK_SUBOBRA AS SUB,
PO.CI_ESTADO AS EST,PO.CI_EUROPA AS EUR,PO.CI_AYTOS AS AYTO,PO.CI_JUNTA AS JUNTA,PO.CI_OTRAS AS OTRAS,
P.DENOMINACION AS DENPLAN,OG.DENOMINACION AS DENOBRA,OG.FK_COD_MUNICIPIO,M.DENOMINACION AS DENMUNI,
NVL(SUM(EI.IMP_AYTO),0) AS SUMA
FROM "ADMINOBRAS".PARTIDAS_OBRAS PO,"ADMINOBRAS".OBRAS_GENERAL OG,"ADMINOBRAS".PLANES P,
"ADMINOBRAS".MUNICIPIOS_PEDANIAS M,"ADMINOBRAS".EVOLUCION_INGRESOS EI
WHERE
(PO.CI_AYTOS IS NOT NULL) AND
(P.COD_PLAN=PO.FK_COD_PLAN) AND
(OG.ANIO_COMIENZO(+)=PO.FK_ANIO_COMIENZO) AND
(OG.FK_COD_PLAN(+)=PO.FK_COD_PLAN) AND
(OG.EXPEDIENTE(+)=PO.FK_EXPEDIENTE) AND
(OG.SUBOBRA(+)=PO.FK_SUBOBRA) AND
(M.COD_MUNICIPIO(+)=OG.FK_COD_MUNICIPIO) AND
(M.COD_PEDANIA(+)=OG.FK_COD_PEDANIA) AND
(EI.FK_ANIO_COMIENZO(+)=PO.FK_ANIO_COMIENZO) AND
(EI.FK_COD_PLAN(+)=PO.FK_COD_PLAN) AND
(EI.FK_EXPEDIENTE(+)=PO.FK_EXPEDIENTE) AND
(EI.FK_SUBOBRA(+)=PO.FK_SUBOBRA) AND
(TO_NUMBER(TO_CHAR(EI.F_ING_AYTO,'YY'))=TO_NUMBER(TO_CHAR(PO.EJERCICIO,'00')))
:PARAMS
GROUP BY
PO.FK_COD_PLAN,PO.FK_ANIO_COMIENZO,PO.EJERCICIO,PO.CI_AYTOS,PO.FK_EXPEDIENTE,
PO.FK_SUBOBRA,PO.CI_ESTADO,PO.CI_EUROPA,PO.CI_JUNTA,PO.CI_OTRAS,
P.DENOMINACION,OG.DENOMINACION,OG.FK_COD_MUNICIPIO,M.DENOMINACION
UNION ALL
SELECT
'JUNTA' AS APORT,PO.FK_COD_PLAN AS PLANN,PO.FK_ANIO_COMIENZO AS ANNO,PO.EJERCICIO,PO.CI_JUNTA AS AGRUP,PO.FK_EXPEDIENTE AS EXP,
PO.FK_SUBOBRA AS SUB,
PO.CI_ESTADO AS EST,PO.CI_EUROPA AS EUR,PO.CI_AYTOS AS AYTO,PO.CI_JUNTA AS JUNTA,PO.CI_OTRAS AS OTRAS,
P.DENOMINACION AS DENPLAN,OG.DENOMINACION AS DENOBRA,OG.FK_COD_MUNICIPIO,M.DENOMINACION AS DENMUNI,
NVL(SUM(EI.IMP_JUNTA),0) AS SUMA
FROM "ADMINOBRAS".PARTIDAS_OBRAS PO,"ADMINOBRAS".OBRAS_GENERAL OG,"ADMINOBRAS".PLANES P,
"ADMINOBRAS".MUNICIPIOS_PEDANIAS M,"ADMINOBRAS".EVOLUCION_INGRESOS EI
WHERE
(PO.CI_JUNTA IS NOT NULL) AND
(P.COD_PLAN=PO.FK_COD_PLAN) AND
(OG.ANIO_COMIENZO(+)=PO.FK_ANIO_COMIENZO) AND
(OG.FK_COD_PLAN(+)=PO.FK_COD_PLAN) AND
(OG.EXPEDIENTE(+)=PO.FK_EXPEDIENTE) AND
(OG.SUBOBRA(+)=PO.FK_SUBOBRA) AND
(M.COD_MUNICIPIO(+)=OG.FK_COD_MUNICIPIO) AND
(M.COD_PEDANIA(+)=OG.FK_COD_PEDANIA) AND
(EI.FK_ANIO_COMIENZO(+)=PO.FK_ANIO_COMIENZO) AND
(EI.FK_COD_PLAN(+)=PO.FK_COD_PLAN) AND
(EI.FK_EXPEDIENTE(+)=PO.FK_EXPEDIENTE) AND
(EI.FK_SUBOBRA(+)=PO.FK_SUBOBRA) AND
(TO_NUMBER(TO_CHAR(EI.F_ING_JUNTA,'YY'))=TO_NUMBER(TO_CHAR(PO.EJERCICIO,'00')))
:PARAMS
GROUP BY
PO.FK_COD_PLAN,PO.FK_ANIO_COMIENZO,PO.EJERCICIO,PO.CI_JUNTA,PO.FK_EXPEDIENTE,
PO.FK_SUBOBRA,PO.CI_ESTADO,PO.CI_EUROPA,PO.CI_AYTOS,PO.CI_OTRAS,
P.DENOMINACION,OG.DENOMINACION,OG.FK_COD_MUNICIPIO,M.DENOMINACION
UNION ALL
SELECT
'OTRAS' AS APORT,PO.FK_COD_PLAN AS PLANN,PO.FK_ANIO_COMIENZO AS ANNO,PO.EJERCICIO,PO.CI_OTRAS AS AGRUP,PO.FK_EXPEDIENTE AS EXP,
PO.FK_SUBOBRA AS SUB,
PO.CI_ESTADO AS EST,PO.CI_EUROPA AS EUR,PO.CI_AYTOS AS AYTO,PO.CI_JUNTA AS JUNTA,PO.CI_OTRAS AS OTRAS,
P.DENOMINACION AS DENPLAN,OG.DENOMINACION AS DENOBRA,OG.FK_COD_MUNICIPIO,M.DENOMINACION AS DENMUNI,
NVL(SUM(EI.IMP_OTROS),0) AS SUMA
FROM "ADMINOBRAS".PARTIDAS_OBRAS PO,"ADMINOBRAS".OBRAS_GENERAL OG,"ADMINOBRAS".PLANES P,
"ADMINOBRAS".MUNICIPIOS_PEDANIAS M,"ADMINOBRAS".EVOLUCION_INGRESOS EI
WHERE
(PO.CI_OTRAS IS NOT NULL) AND
(P.COD_PLAN=PO.FK_COD_PLAN) AND
(OG.ANIO_COMIENZO(+)=PO.FK_ANIO_COMIENZO) AND
(OG.FK_COD_PLAN(+)=PO.FK_COD_PLAN) AND
(OG.EXPEDIENTE(+)=PO.FK_EXPEDIENTE) AND
(OG.SUBOBRA(+)=PO.FK_SUBOBRA) AND
(M.COD_MUNICIPIO(+)=OG.FK_COD_MUNICIPIO) AND
(M.COD_PEDANIA(+)=OG.FK_COD_PEDANIA) AND
(EI.FK_ANIO_COMIENZO(+)=PO.FK_ANIO_COMIENZO) AND
(EI.FK_COD_PLAN(+)=PO.FK_COD_PLAN) AND
(EI.FK_EXPEDIENTE(+)=PO.FK_EXPEDIENTE) AND
(EI.FK_SUBOBRA(+)=PO.FK_SUBOBRA) AND
(TO_NUMBER(TO_CHAR(EI.F_ING_OTROS,'YY'))=TO_NUMBER(TO_CHAR(PO.EJERCICIO,'00')))
:PARAMS
GROUP BY
PO.FK_COD_PLAN,PO.FK_ANIO_COMIENZO,PO.EJERCICIO,PO.CI_OTRAS,PO.FK_EXPEDIENTE,
PO.FK_SUBOBRA,PO.CI_ESTADO,PO.CI_EUROPA,PO.CI_AYTOS,PO.CI_JUNTA,
P.DENOMINACION,OG.DENOMINACION,OG.FK_COD_MUNICIPIO,M.DENOMINACION
ORDER BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16