Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

ORA-01427: single-row subquery returns more than one row.

Júnior JúniorMar 29 2024

I'm trying to run a PL/SQL process and it's getting me the error:

_**declare**_
_**v_tipo varchar(100);**_
_**begin**_

_**if**_
_**:P2_TIPO != 'Outros' and :P2_FORMA_PAGAMENTO != 'Outras'**_
_**then**_
_**insert into detalhamento(tipo, forma_pagamento, beneficiario, descricao, valor, data)**_
_**values(:P2_TIPO, :P2_FORMA_PAGAMENTO, :P2_BENEFICIARIO, :P2_DESCRICAO, :P2_VALOR, :P2_DATA);**_

_**update porc_orcamento set**_
_**porc_salario = (select sum(valor)/(select sum(salario) from receita where fonte = 'Trabalho' and SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento)) ) * 100 ||'%' from detalhamento where SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento)) group by valor),**_
_**porc_desp = (select sum(valor)/(select sum(valor) from detalhamento where SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento) ))*100 ||'%' from detalhamento where tipo = (select tipo from detalhamento where tstamp = (select max(tstamp) from detalhamento)) and SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento)) group by valor),**_
_**despesas = (select sum(valor) from detalhamento where tipo = (select tipo from detalhamento where tstamp = (select max(tstamp) from detalhamento)) and SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento)) ),**_
_**diferenca = ((select custos - despesas from (select previsao_custos custos from porc_orcamento where previsao_nomes = (select tipo from detalhamento where tstamp = (select max(tstamp) from detalhamento)) and SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento)) ), (select sum(valor) despesas from detalhamento where tipo = (select tipo from detalhamento where tstamp = (select max(tstamp) from detalhamento)) and SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento)) )))**_
_**where previsao_nomes = (select tipo from detalhamento where tstamp = (select max(tstamp) from detalhamento)) and SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento));**_

_**end if;**_

_**if**_
_**:P2_TIPO = 'Outros' and :P2_FORMA_PAGAMENTO != 'Outras'**_
_**then**_

_**insert into detalhamento(tipo, forma_pagamento, beneficiario, descricao, valor, data)**_
_**values(:P2_OUTRO_TIPO, :P2_FORMA_PAGAMENTO, :P2_BENEFICIARIO, :P2_DESCRICAO, :P2_VALOR, :P2_DATA);**_

_**select count(previsao_nomes) into v_tipo from porc_orcamento where previsao_nomes = (select tipo from detalhamento where tstamp = (select max(tstamp) from detalhamento)) and SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento)) ;**_

_**if**_
_**v_tipo = 0**_
_**then**_
_**insert into porc_orcamento(previsao_nomes, data)**_
_**select (select tipo from detalhamento where tstamp = (select max(tstamp) from detalhamento)),**_
_**(select data from detalhamento where tstamp = (select max(tstamp) from detalhamento))**_
_**from dual;**_

_**end if;**_

_**update porc_orcamento set**_
_**porc_salario = (select sum(valor)/(select sum(salario) from receita where fonte = 'Trabalho' and SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento)) ) * 100 ||'%' from detalhamento where SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento)) group by valor),**_
_**porc_desp = (select sum(valor)/(select sum(valor) from detalhamento where SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento) ))*100 ||'%' from detalhamento where tipo = (select tipo from detalhamento where tstamp = (select max(tstamp) from detalhamento)) and SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento)) group by valor),**_
_**despesas = (select sum(valor) from detalhamento where tipo = (select tipo from detalhamento where tstamp = (select max(tstamp) from detalhamento)) and SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento)) ),**_
_**diferenca = ((select custos - despesas from (select previsao_custos custos from porc_orcamento where previsao_nomes = (select tipo from detalhamento where tstamp = (select max(tstamp) from detalhamento)) and SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento)) ), (select sum(valor) despesas from detalhamento where tipo = (select tipo from detalhamento where tstamp = (select max(tstamp) from detalhamento)) and SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento)) )))**_
_**where previsao_nomes = (select tipo from detalhamento where tstamp = (select max(tstamp) from detalhamento)) and SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento));**_

_**end if;**_

_**if**_
_**:P2_TIPO != 'Outros' and :P2_FORMA_PAGAMENTO = 'Outras'**_
_**then**_
_**insert into detalhamento(tipo, forma_pagamento, beneficiario, descricao, valor, data)**_
_**values(:P2_TIPO, :P2_OUTRA_FORMA, :P2_BENEFICIARIO, :P2_DESCRICAO, :P2_VALOR, :P2_DATA);**_
_**end if;**_

_**if**_
_**:P2_TIPO = 'Outros' and :P2_FORMA_PAGAMENTO = 'Outras'**_
_**then**_
_**insert into detalhamento(tipo, forma_pagamento, beneficiario, descricao, valor, data)**_
_**values(:P2_OUTRO_TIPO, :P2_OUTRA_FORMA, :P2_BENEFICIARIO, :P2_DESCRICAO, :P2_VALOR, :P2_DATA);**_

_**select count(previsao_nomes) into v_tipo from porc_orcamento where previsao_nomes = (select tipo from detalhamento where tstamp = (select max(tstamp) from detalhamento)) and SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento)) ;**_

_**if**_
_**v_tipo = 0**_
_**then**_
_**insert into porc_orcamento(previsao_nomes, data)**_
_**select (select tipo from detalhamento where tstamp = (select max(tstamp) from detalhamento)),**_
_**(select data from detalhamento where tstamp = (select max(tstamp) from detalhamento))**_
_**from dual;**_
_**end if;**_

_**update porc_orcamento set**_
_**porc_salario = (select sum(valor)/(select sum(salario) from receita where fonte = 'Trabalho' and SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento)) ) * 100 ||'%' from detalhamento where SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento)) group by valor),**_
_**porc_desp = (select sum(valor)/(select sum(valor) from detalhamento where SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento) ))*100 ||'%' from detalhamento where tipo = (select tipo from detalhamento where tstamp = (select max(tstamp) from detalhamento)) and SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento)) group by valor),**_
_**despesas = (select sum(valor) from detalhamento where tipo = (select tipo from detalhamento where tstamp = (select max(tstamp) from detalhamento)) and SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento)) ),**_
_**diferenca = ((select custos - despesas from (select previsao_custos custos from porc_orcamento where previsao_nomes = (select tipo from detalhamento where tstamp = (select max(tstamp) from detalhamento)) and SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento)) ), (select sum(valor) despesas from detalhamento where tipo = (select tipo from detalhamento where tstamp = (select max(tstamp) from detalhamento)) and SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento)) )))**_
_**where previsao_nomes = (select tipo from detalhamento where tstamp = (select max(tstamp) from detalhamento)) and SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) = (select SUBSTR(EXTRACT(MONTH FROM data), -1) ||'/'|| SUBSTR(EXTRACT(YEAR FROM data), -2) from detalhamento where tstamp = (select max(tstamp) from detalhamento));**_

_**end if;**_
_**end;**_

I've tried to solve this problem in every way I know, but I couldn't. Any suggestions?

Comments
Post Details
Added on Mar 29 2024
2 comments
77 views