SELECT TRIM(e.APELLIDO1) "PRIMER APELLIDO",
TRIM(e.APELLIDO2) "SEGUNDO APELLIDO",
TRIM(e.NOMBRE1)||' '||TRIM(e.NOMBRE2) "NOMBRES",
e.NUMDOCUMENTO AS "DOCUMENTO DE IDENTIDAD",
e.SEXO AS "GENERO",
(CASE WHEN c.CODCARGOTIPO = 1 THEN 'DOCENTE' ELSE 'DIRECTIVO DOCENTE' END)AS "CARGO",
ce.CARGOEMPRESA AS "NOMBRE DEL CARGO",
vd.FECHAINI,MIN(es.CODESCALAFONSOLICITUD) AS "COD SOLICUTUD ESCA."
FROM EMPLEADO_TPP e,
VINCULACION_TPP v,
VINCULACIONDET_TPP vd,
CARGOEMPRESA_TPP ce,
CARGO_TPP c,
ESCALAFONSOLICITUD_TGE es
WHERE e.CODEMPLEADO = v.CODEMPLEADO
AND v.NUMVINCULACION = vd.NUMVINCULACION
AND ce.CODCARGOEMPRESA = NVL(vd.CODENCARGO, vd.CODCARGOEMPRESA)
AND ce.CODCARGO = c.CODCARGO
AND c.CODCARGOTIPO IN(1,2)
AND vd.GRADO NOT IN('01','02','03','04','05','06','07','08','09','10','11','12','13','14')
AND vd.CODNIVELCONTRATACION = 1
AND vd.CODVINCULACIONNOVEDAD = 31
AND vd.FECHAINI IS NOT NULL
AND v.CODVINCULACIONESTADO = 1
AND vd.FECHAINI =
(SELECT MIN(vd2.FECHAINI)
FROM VINCULACIONDET_TPP vd2
WHERE v.NUMVINCULACION = vd2.NUMVINCULACION
AND vd2.FECHAINI IS NOT NULL
AND vd2.CODNIVELCONTRATACION = 1)
AND es.CODESCALAFONSOLICITUD = (SELECT MIN(es2.CODESCALAFONSOLICITUD)
FROM ESCALAFONSOLICITUD_TGE es2
WHERE e.CODEMPLEADO =es2.CODEMPLEADO)
the query up to that point is fine because it does what it is asked:
Bring the users, select the first (MIN) work link and select the first (MIN) request for promotion, I need the query to include the other users so they have not made a request for promotion, then in the field es.CODESCALAFONSOLICITUD
for Pedro Perez who has never made a request leave something like "WITHOUT REGISTRATION".