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.Varnomusuariodestin o FROM SOLICITUDSEGUIMIENTO SOLREC
WHERE
SOLREC.INTIDNUMEROPROVISIO N=SOL.INTI DNUMEROPRO VISION AND
SOLREC.INTIDDECISION IN (14,15,16,26,41,63,37,38) AND
SOLREC.INTTIPOSEGUIMIENTO= 1 AND
SOLREC.INTIDSOLICITUDSEGUI MIENTO = (
SELECT MAX(SOLRECR.INTIDSOLICITUD SEGUIMIENT O) FROM SOLICITUDSEGUIMIENTO SOLRECR
WHERE SOLRECR.INTIDNUMEROPROVISI ON=SOL.INT IDNUMEROPR OVISION AND
SOLRECR.INTIDDECISION IN (14,15,16,26,41,63,37,38) AND
SOLRECR.INTTIPOSEGUIMIENTO = 1
)) AS "Usuario Destino 1",
(
select distinct(sr.varnomanalista reclu)
from solicitudreclutamiento sr
where sr.intidnumeroprovisionori = SOL.intidnumeroprovision) as "Usuario Destino 2",
(SELECT SOLREC.Varestaciondestino FROM SOLICITUDSEGUIMIENTO SOLREC
WHERE
SOLREC.INTIDNUMEROPROVISIO N=SOL.INTI DNUMEROPRO VISION AND
SOLREC.INTIDDECISION IN (14,15,16,26,41,63,37,38) AND
SOLREC.INTTIPOSEGUIMIENTO= 1 AND
SOLREC.INTIDSOLICITUDSEGUI MIENTO = (
SELECT MAX(SOLRECR.INTIDSOLICITUD SEGUIMIENT O) FROM SOLICITUDSEGUIMIENTO SOLRECR
WHERE SOLRECR.INTIDNUMEROPROVISI ON=SOL.INT IDNUMEROPR OVISION 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=S S.INTIDNUM EROPROVISI ON AND
SOL.INTIDPROCESO=SS.INTIDP ROCESO
INNER JOIN PASO PA ON SS.INTIDPASO=PA.INTIDPASO AND SS.INTIDPROCESO=PA.INTIDPR OCESO
WHERE
UPPER(SS.varusuarioregistr o) IN ('GRUPOIB.LOCAL/B10650')
AND SS.INTIDSOLICITUDSEGUIMIEN TO IN
(
SELECT MAX(SSE.INTIDSOLICITUDSEGU IMIENTO)
FROM SOLICITUDSEGUIMIENTO SSE
WHERE
SOL.INTIDNUMEROPROVISION=S SE.INTIDNU MEROPROVIS ION AND
SSE.INTIDDECISION IN (14,15,16,26,41,63,37,38) AND
UPPER(SSE.varusuarioregist ro) IN (UPPER('GRUPOIB.LOCAL/B106 50'))
GROUP BY SSE.INTIDNUMEROPROVISION
)
userESTACIONdestino.jpg
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.Varnomusuariodestin
WHERE
SOLREC.INTIDNUMEROPROVISIO
SOLREC.INTIDDECISION IN (14,15,16,26,41,63,37,38) AND
SOLREC.INTTIPOSEGUIMIENTO=
SOLREC.INTIDSOLICITUDSEGUI
SELECT MAX(SOLRECR.INTIDSOLICITUD
WHERE SOLRECR.INTIDNUMEROPROVISI
SOLRECR.INTIDDECISION IN (14,15,16,26,41,63,37,38) AND
SOLRECR.INTTIPOSEGUIMIENTO
)) AS "Usuario Destino 1",
(
select distinct(sr.varnomanalista
from solicitudreclutamiento sr
where sr.intidnumeroprovisionori
(SELECT SOLREC.Varestaciondestino FROM SOLICITUDSEGUIMIENTO SOLREC
WHERE
SOLREC.INTIDNUMEROPROVISIO
SOLREC.INTIDDECISION IN (14,15,16,26,41,63,37,38) AND
SOLREC.INTTIPOSEGUIMIENTO=
SOLREC.INTIDSOLICITUDSEGUI
SELECT MAX(SOLRECR.INTIDSOLICITUD
WHERE SOLRECR.INTIDNUMEROPROVISI
SOLRECR.INTIDDECISION IN (14,15,16,26,41,63,37,38) AND
SOLRECR.INTTIPOSEGUIMIENTO
)) AS "Estacion Destino 1",
(
select distinct (cast('6.1 Revision de Perfil' as varchar2(80)))
from solicitudreclutamiento sr
where sr.intidnumeroprovisionori
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=S
SOL.INTIDPROCESO=SS.INTIDP
INNER JOIN PASO PA ON SS.INTIDPASO=PA.INTIDPASO AND SS.INTIDPROCESO=PA.INTIDPR
WHERE
UPPER(SS.varusuarioregistr
AND SS.INTIDSOLICITUDSEGUIMIEN
(
SELECT MAX(SSE.INTIDSOLICITUDSEGU
FROM SOLICITUDSEGUIMIENTO SSE
WHERE
SOL.INTIDNUMEROPROVISION=S
SSE.INTIDDECISION IN (14,15,16,26,41,63,37,38) AND
UPPER(SSE.varusuarioregist
GROUP BY SSE.INTIDNUMEROPROVISION
)
userESTACIONdestino.jpg
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER