Include columns without records

0
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".

    
asked by DIANGA 18.05.2016 в 17:33
source

0 answers