Error ORA-01427

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Fri, 30 Aug 2013 10:34 am

I'm trying to use a select statement that returns the following error: ORA-01427 single-row subquery returns more than one row Follows the query
Code: Select all
select
       case when c.fisjur in 'F' then
         (SELECT f.cpf from fat_pessoa_fisica f)
        else
         (SELECT to_number(j.cgc) from fat_pessoa_juridica j)
        end CPF_CNPJ
from fat_cliente c
what would be causing this error? : shock:
danilo_zg
Location: Jundiaí - SP

Poston Fri, 30 Aug 2013 2:48 pm

What would be causing this error is the lack of understanding of the subselect. :P. When you do in place of the field from the table:
Code: Select all
case when c.fisjur in 'F' then
(SELECT f.cpf from fat_pessoa_fisica f)
você returns all rows of the table should contain only one, the select principal.
The select waits just a given and você returns EVERY set of data from the table, appearing the " returns more than one row " (Returns more than one row).

What you must do is ensure that your desired line contains only subselect method, passing the key or reference id. How to:
Code: Select all
select
case when c.fisjur in 'F' then
(SELECT f.cpf from fat_pessoa_fisica f where f.cliente_id = c.cliente_id)
else
(SELECT to_number(j.cgc) from fat_pessoa_juridica j where j.cliente_id = c.cliente_id)
end CPF_CNPJ
from fat_cliente c
DanielNN
Location: Fortaleza - CE

att,

Daniel N.N.

Poston Fri, 30 Aug 2013 3:00 pm

Noctifero, thank you very much for your help my friend. : mrgreen: Now the error made sense to me. : idea:
danilo_zg
Location: Jundiaí - SP



Return to SQL

Who is online

Users browsing this forum: Bing [Bot] and 5 guests