?
Solved

ORA-00907: missing right parenthesis

Posted on 2011-05-04
12
Medium Priority
?
908 Views
Last Modified: 2012-05-11
My full query has more than 20 columns, I have an error adding your query, please help me
queryINCOMPLETO.txt
0
Comment
Question by:enrique_aeo
  • 6
  • 4
11 Comments
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1600 total points
ID: 35691095
Look at what I posted in your other question.  You need to close out the open paran after the from:


select
      nvl("Usuario Destino 1", "Usuario Destino 2") "Usuario Destino",
      nvl("Estacion Destino 1", "Estación Destino 2") "Estacion Destino"
from
---missing the closing one for this one
     (
....

--THIS one is the one you appear to be missing it should be at the very bottom.
)
0
 

Author Comment

by:enrique_aeo
ID: 35691217
apply your changes but does not work
view file please
queryINCOMPLETO.txt
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1600 total points
ID: 35691283
Sorry.  I don't have the time to walk through all that code to see what you are trying to do.

I didn't say to copy/paste what I posted into your code and it would work.

From what you asked for yesterday, the NVL will return the values you asked for.

How you are trying to incorporate that into the much larger query, is up to you.

I can say that what you just posted isn't syntactically correct.

The final outer select needs to return individual columns.  You are pasting the NVL code I provided into the MIDDLE of a much larger query and it doesn't return a single column value.

For example you cannot:
select 'hello' as "some string", (select 'a','b' from dual) as "some value"
from dual;

You will get: ERROR at line 1:  ORA-00913: too many values

It looks like that is what you are trying to do.

To find the missing parans, I suggest you get a text editor of some type that will tag/mark or find matching parans.   If you cannot, at least indent on each set so you can easily see them.






0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:enrique_aeo
ID: 35694472
I am new to pl / sql and unfortunately I have had a complex query, I need your help
querySMALLresults.jpg
query-big.txt
query-small.txt
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1600 total points
ID: 35694518
I understand what you are trying to do.  I'm just saying as-is, it is not possible in SQL.  At least in Oracle, you cannot use inline views to return more than one columns/value.

The quick and easy is to just repeat the code twice (one value each).  The code below cannot be tested but should give you the idea.



I've taken several looks at your SQL and really think it can be greatly simplified.  You seem to do a lot of selects and sub-selects against the same base tables.  This tells me you can probably combine several of them.

To simplify it, someone will have to have a solid understanding of the tables involved and the data and how they all interact.

I would really suggest you contract with a local SQL expert to spend some time with you.  This will allow you to write a lot more efficient SQL.  Your users will thank you.


SELECT 
    SOL.VARETIQPROVISION AS "Nro. Solicitud",
    PRO.VARNOMPROCESO as "Proceso",
    SOL.DATFECHAREGISTRO as "Fec.Recep.",
    cast(to_date(decode(sol.intidestadosolicitud,98,(select max(datfechaenvio) from solicitudseguimiento where intidnumeroprovision =sol.intidnumeroprovision and inttiposeguimiento = 1),
                                           15,(select max(datfechaenvio) from solicitudseguimiento where intidnumeroprovision =sol.intidnumeroprovision and inttiposeguimiento = 1),
                                              decode(sol.datfechaeliminacion, null,sysdate,sol.datfechaeliminacion))) - to_date(SOL.datFechaCreacion) as integer)  AS "Días Trans.",
    SOL.VARNOMVICEPRESIDENCIA as "Vicepresidencia",
    SOL.VARNOMGERENCIADIVISION as "División",
    SOL.VARNOMDEPARTAMENTO as "Departamento",
    CASE  
    WHEN SOL.intIdProceso = 1 OR SOL.intIdProceso=2 THEN
                        CASE SOL.VAREXISTEPRESUPUESTO
                        WHEN '1' THEN 'SI'
                        WHEN '0' THEN 'NO' 
                        else '' END
    ELSE '' END
    AS "Pres.", 
    (SELECT PKG_WRSPROVISION_Consulta.WRS_FN_ObtenerCargo(SOL.INTIDNUMEROPROVISION,SOL.intIdProceso) FROM DUAL) as "Cargo",
    Pkg_Wrsprovision_Consulta.WRS_FN_ObtenerEstado(SOL.VARETIQPROVISION,SOL.INTIDNUMEROPROVISION,1) as "Estado",
    NVL(SOL.INTCANTIDADPERSONAS,0) AS "Cant",
    (SELECT SOLREC.VARNOMUSUARIOENVIO 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 Origen",
    PA.varnompasoultimus as "Estación Origen",
(
select nvl("Usuario Destino 1", "Usuario Destino 2")
from
(
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
                  )      
)
) "Usuario Destino",
(
select nvl("Estacion Destino 1", "Estación Destino 2")
from
(
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
                  )      
)
) "Estacion Destino"



-------------------end query small
    SOL.INTIDNUMEROPROVISION,
    cast('   ' as varchar2(80)) as "NroTaskId",
    SOL.VARETIQPROVISION AS "VARETIQPROVISION",
    SOL.INTIDPROCESO AS INTIDTIPOSOLICITUD,
    PA.varnompasoultimus as "ESTACIONREF",
    UPPER(PRO.VARNOMPROCESO) as "PROCESOREF"

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
                  )

Open in new window

0
 

Author Comment

by:enrique_aeo
ID: 35694537
I HAVE THIS ERROR
errQUERY.jpg
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35694555
You have to help a little on basic syntax errors.  Hopefully it is because I forgot a comment and depending on the tool, left some blank lines.

See if this clears up that error.
SELECT 
    SOL.VARETIQPROVISION AS "Nro. Solicitud",
    PRO.VARNOMPROCESO as "Proceso",
    SOL.DATFECHAREGISTRO as "Fec.Recep.",
    cast(to_date(decode(sol.intidestadosolicitud,98,(select max(datfechaenvio) from solicitudseguimiento where intidnumeroprovision =sol.intidnumeroprovision and inttiposeguimiento = 1),
                                           15,(select max(datfechaenvio) from solicitudseguimiento where intidnumeroprovision =sol.intidnumeroprovision and inttiposeguimiento = 1),
                                              decode(sol.datfechaeliminacion, null,sysdate,sol.datfechaeliminacion))) - to_date(SOL.datFechaCreacion) as integer)  AS "Días Trans.",
    SOL.VARNOMVICEPRESIDENCIA as "Vicepresidencia",
    SOL.VARNOMGERENCIADIVISION as "División",
    SOL.VARNOMDEPARTAMENTO as "Departamento",
    CASE  
    WHEN SOL.intIdProceso = 1 OR SOL.intIdProceso=2 THEN
                        CASE SOL.VAREXISTEPRESUPUESTO
                        WHEN '1' THEN 'SI'
                        WHEN '0' THEN 'NO' 
                        else '' END
    ELSE '' END
    AS "Pres.", 
    (SELECT PKG_WRSPROVISION_Consulta.WRS_FN_ObtenerCargo(SOL.INTIDNUMEROPROVISION,SOL.intIdProceso) FROM DUAL) as "Cargo",
    Pkg_Wrsprovision_Consulta.WRS_FN_ObtenerEstado(SOL.VARETIQPROVISION,SOL.INTIDNUMEROPROVISION,1) as "Estado",
    NVL(SOL.INTCANTIDADPERSONAS,0) AS "Cant",
    (SELECT SOLREC.VARNOMUSUARIOENVIO 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 Origen",
    PA.varnompasoultimus as "Estación Origen",
(
select nvl("Usuario Destino 1", "Usuario Destino 2")
from
(
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
                  )      
)
) "Usuario Destino",
(
select nvl("Estacion Destino 1", "Estación Destino 2")
from
(
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
                  )      
)
) "Estacion Destino",
    SOL.INTIDNUMEROPROVISION,
    cast('   ' as varchar2(80)) as "NroTaskId",
    SOL.VARETIQPROVISION AS "VARETIQPROVISION",
    SOL.INTIDPROCESO AS INTIDTIPOSOLICITUD,
    PA.varnompasoultimus as "ESTACIONREF",
    UPPER(PRO.VARNOMPROCESO) as "PROCESOREF"
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
                  )

Open in new window

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35694556
missed more blank lines...  just in case.
SELECT 
    SOL.VARETIQPROVISION AS "Nro. Solicitud",
    PRO.VARNOMPROCESO as "Proceso",
    SOL.DATFECHAREGISTRO as "Fec.Recep.",
    cast(to_date(decode(sol.intidestadosolicitud,98,(select max(datfechaenvio) from solicitudseguimiento where intidnumeroprovision =sol.intidnumeroprovision and inttiposeguimiento = 1),
                                           15,(select max(datfechaenvio) from solicitudseguimiento where intidnumeroprovision =sol.intidnumeroprovision and inttiposeguimiento = 1),
                                              decode(sol.datfechaeliminacion, null,sysdate,sol.datfechaeliminacion))) - to_date(SOL.datFechaCreacion) as integer)  AS "Días Trans.",
    SOL.VARNOMVICEPRESIDENCIA as "Vicepresidencia",
    SOL.VARNOMGERENCIADIVISION as "División",
    SOL.VARNOMDEPARTAMENTO as "Departamento",
    CASE  
    WHEN SOL.intIdProceso = 1 OR SOL.intIdProceso=2 THEN
                        CASE SOL.VAREXISTEPRESUPUESTO
                        WHEN '1' THEN 'SI'
                        WHEN '0' THEN 'NO' 
                        else '' END
    ELSE '' END
    AS "Pres.", 
    (SELECT PKG_WRSPROVISION_Consulta.WRS_FN_ObtenerCargo(SOL.INTIDNUMEROPROVISION,SOL.intIdProceso) FROM DUAL) as "Cargo",
    Pkg_Wrsprovision_Consulta.WRS_FN_ObtenerEstado(SOL.VARETIQPROVISION,SOL.INTIDNUMEROPROVISION,1) as "Estado",
    NVL(SOL.INTCANTIDADPERSONAS,0) AS "Cant",
    (SELECT SOLREC.VARNOMUSUARIOENVIO 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 Origen",
    PA.varnompasoultimus as "Estación Origen",
(
select nvl("Usuario Destino 1", "Usuario Destino 2")
from
(
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
                  )      
)
) "Usuario Destino",
(
select nvl("Estacion Destino 1", "Estación Destino 2")
from
(
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
                  )      
)
) "Estacion Destino",
    SOL.INTIDNUMEROPROVISION,
    cast('   ' as varchar2(80)) as "NroTaskId",
    SOL.VARETIQPROVISION AS "VARETIQPROVISION",
    SOL.INTIDPROCESO AS INTIDTIPOSOLICITUD,
    PA.varnompasoultimus as "ESTACIONREF",
    UPPER(PRO.VARNOMPROCESO) as "PROCESOREF"
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
                  )

Open in new window

0
 

Author Comment

by:enrique_aeo
ID: 35694571
this is another error came out when I try to gather the querys
errQUERY.jpg
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1600 total points
ID: 35694592
OK, this means exactly what it says.  I should have caught this from what you posted.  An inline query as a column can only return one row.

The image of the output you provided in http:#a35694472 shows it returns several rows.  This is the reason for the error.

You will likely need to move this select as a view not a inline column.  Then you can add the necessary joins to get the single column for the correct row.

Since I don't know your setup I really cannot help you rewrite this.  Since I cannot test anything, I'm basically guessing.

I suggest you click the 'Request Attention' link above and ask a Moderator to see if they can find someone that might be able to rewrite this for you.
0
 
LVL 71

Assisted Solution

by:Qlemo
Qlemo earned 400 total points
ID: 35704441
You need to rewrite your query. Don't use inline selects for column values, instead use "ad-hoc views" (can't remember the proper ANSI SQL name for it), according to this example:

select a.one, b.two
from tbl1 a join (select one, max(two) two, sum(three) three, count(four) four from tbl2 group by one) b
on a.one = b.one
join ...

That gives you much better control over your select results, and will certainly simplify your enormous query.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month14 days, 5 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question