CODEWITHSUNDEEP
sqloracle-database
aroldan
aroldan

Reputation: 1

ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis", while on a subquery with all parenthesis matching

Hi I have been trying to do a subquery using 2 tables and when I try to run the following code:

select t.cantidad_traslado,
        (select sum(te.coste)"Coste", 
            (case when te.nif_emptransporte='F-98987667-R' then'AceSur'
                  when te.nif_emptransporte='A-98985367-V' then'TransMadrid'
                  when te.nif_emptransporte='A-97654567-S' then'Perez e Hijos'
                  when te.nif_emptransporte='A-87684567-B' then'Resur'
                  when te.nif_emptransporte='A-98987067-V' then'HuelResi'
                  else 'Indefinido'
                  end)"Empresa"
        from traslado_empresatransportista te
        group by te.nif_emptransporte
        where t.nif_empresa=te.nif_empresa)    
from traslado t
where t.nif_empresa in (select distinct nif_empresa from traslado_empresatransportista)

but when I try to run it oracle shows me the following error:

ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause:
*Action: Error at Line: 12 Column: 9.

I am pretty sure I have all of my parenthesis right, so I wanted to know if yall could help me.

Edit:

Someone suggestes I swapped the order between the where and group by clause like this:

select t.cantidad_traslado,
        (select sum(te.coste)"Coste", 
            (case when te.nif_emptransporte='F-98987667-R' then'AceSur'
                  when te.nif_emptransporte='A-98985367-V' then'TransMadrid'
                  when te.nif_emptransporte='A-97654567-S' then'Perez e Hijos'
                  when te.nif_emptransporte='A-87684567-B' then'Resur'
                  when te.nif_emptransporte='A-98987067-V' then'HuelResi'
                  else 'Indefinido'
                  end)"Empresa"
        from traslado_empresatransportista te
        group by te.nif_emptransporte
        where t.nif_empresa=te.nif_empresa)    
from traslado t
where t.nif_empresa in (select distinct nif_empresa from traslado_empresatransportista)

When I did I was presented the following error:

ORA-00913: too many values 00913. 00000 - "too many values" *Cause:
*Action:

Upvotes: 0

Views: 72

Answers (1)

MT0
MT0

Reputation: 167981

From Oracle 12, you can use a LATERAL join and then return multiple columns from the joined table:

select t.cantidad_traslado,
       te.*
from   traslado t
       CROSS JOIN LATERAL (
         select sum(te.coste) AS "Coste", 
                case
                when te.nif_emptransporte='F-98987667-R' then'AceSur'
                when te.nif_emptransporte='A-98985367-V' then'TransMadrid'
                when te.nif_emptransporte='A-97654567-S' then'Perez e Hijos'
                when te.nif_emptransporte='A-87684567-B' then'Resur'
                when te.nif_emptransporte='A-98987067-V' then'HuelResi'
                else 'Indefinido'
                end AS "Empresa"
         from   traslado_empresatransportista te
         where  t.nif_empresa=te.nif_empresa
         group by te.nif_emptransporte
       ) te

Upvotes: 0

Related Questions