Link to home
Start Free TrialLog in
Avatar of enrique_aeo
enrique_aeo

asked on

query in oracle

I need to show 2 columns: usuario_destino and estacion_destino
 Currently I am showing 4
 usuario_destino_1, estacion_destino_1, usuario_destino_2, estacion_destino_2
 What I do is the following:
 1. When usuario_destino_2 usuario_destino_1 has value and has no value, then I show usuario_destino_1
 2. When estacion_destino_2 estacion_destino_1 has value and has no value, then I show estacion_destino_1
desired information is displayed in the yellow attachment (view file)

I have this query
SELECT
SOL.VARETIQPROVISION AS "Nro. Solicitud",
(SELECT SOLREC.Varnomusuariodestino FROM SOLICITUDSEGUIMIENTO SOLREC
WHERE
SOLREC.INTIDNUMEROPROVISION=SOL.INTIDNUMEROPROVISION AND
SOLREC.INTIDDECISION IN (14,15,16,26,41,63,37,38) AND
SOLREC.INTTIPOSEGUIMIENTO= 1 AND
SOLREC.INTIDSOLICITUDSEGUIMIENTO = (
SELECT MAX(SOLRECR.INTIDSOLICITUDSEGUIMIENTO) FROM SOLICITUDSEGUIMIENTO SOLRECR
WHERE SOLRECR.INTIDNUMEROPROVISION=SOL.INTIDNUMEROPROVISION AND
SOLRECR.INTIDDECISION IN (14,15,16,26,41,63,37,38) AND
SOLRECR.INTTIPOSEGUIMIENTO= 1
)) AS "Usuario Destino 1",
(
  select distinct(sr.varnomanalistareclu)
  from solicitudreclutamiento sr
  where sr.intidnumeroprovisionori = SOL.intidnumeroprovision) as "Usuario Destino 2",
(SELECT SOLREC.Varestaciondestino FROM SOLICITUDSEGUIMIENTO SOLREC
WHERE
SOLREC.INTIDNUMEROPROVISION=SOL.INTIDNUMEROPROVISION AND
SOLREC.INTIDDECISION IN (14,15,16,26,41,63,37,38) AND
SOLREC.INTTIPOSEGUIMIENTO= 1 AND
SOLREC.INTIDSOLICITUDSEGUIMIENTO = (
SELECT MAX(SOLRECR.INTIDSOLICITUDSEGUIMIENTO) FROM SOLICITUDSEGUIMIENTO SOLRECR
WHERE SOLRECR.INTIDNUMEROPROVISION=SOL.INTIDNUMEROPROVISION AND
SOLRECR.INTIDDECISION IN (14,15,16,26,41,63,37,38) AND
SOLRECR.INTTIPOSEGUIMIENTO= 1
)) AS "Estacion Destino 1",
(
  select distinct (cast('6.1 Revision de Perfil' as varchar2(80)))
  from solicitudreclutamiento sr
  where sr.intidnumeroprovisionori = SOL.intidnumeroprovision) as "Estación Destino 2"

FROM SOLICITUD SOL
  INNER JOIN PROCESO PRO ON SOL.intIdProceso = PRO.intIdProceso
  INNER JOIN PARAMETROS PAR ON SOL.intIdEstadoSolicitud = PAR.intCodParametro
  INNER JOIN SOLICITUDSEGUIMIENTO SS ON
             SOL.INTIDNUMEROPROVISION=SS.INTIDNUMEROPROVISION AND
               SOL.INTIDPROCESO=SS.INTIDPROCESO
INNER JOIN PASO PA ON SS.INTIDPASO=PA.INTIDPASO AND SS.INTIDPROCESO=PA.INTIDPROCESO
WHERE
UPPER(SS.varusuarioregistro) IN ('GRUPOIB.LOCAL/B10650')
 AND SS.INTIDSOLICITUDSEGUIMIENTO IN
                 (
                  SELECT MAX(SSE.INTIDSOLICITUDSEGUIMIENTO)
                  FROM SOLICITUDSEGUIMIENTO SSE
                  WHERE
                       SOL.INTIDNUMEROPROVISION=SSE.INTIDNUMEROPROVISION AND
                       SSE.INTIDDECISION IN (14,15,16,26,41,63,37,38) AND
                       UPPER(SSE.varusuarioregistro) IN (UPPER('GRUPOIB.LOCAL/B10650'))
                       GROUP BY SSE.INTIDNUMEROPROVISION
                  )      

userESTACIONdestino.jpg
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of enrique_aeo
enrique_aeo

ASKER

NOT UNDERSTAND, you can put the full code please
Not really.  I can only guess since I don't have your setup I cannot test anything.

Just wrap the NVL call I provided around whatever returns usuario_destino_1 and  usuario_destino_2.  Again for the other two.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial