SQL Error no Postgres

Asked

Viewed 98 times

0

My select: 

SELECT * (
          SELECT  v.placa FROM gr_veiculo v WHERE v.id = (
                 SELECT DISTINCT r.cavalo FROM lg_relacao_veiculo r 
                 WHERE (
                   r.cavalo   = (SELECT mv.gr_veiculo_id FROM mt_solicitacao_manutencao_veiculo mv WHERE mv.mt_solicitacao_manutencao_id = sm.id AND mv.ativo = TRUE ORDER BY id DESC LIMIT 1) OR
                   r.carreta1 = (SELECT mv.gr_veiculo_id FROM mt_solicitacao_manutencao_veiculo mv WHERE mv.mt_solicitacao_manutencao_id = sm.id AND mv.ativo = TRUE ORDER BY id DESC LIMIT 1) OR
                   r.carreta2 = (SELECT mv.gr_veiculo_id FROM mt_solicitacao_manutencao_veiculo mv WHERE mv.mt_solicitacao_manutencao_id = sm.id AND mv.ativo = TRUE ORDER BY id DESC LIMIT 1) 
                 ) and r.cavalo is not null
          )
        ) AS placa,       
       sm.mt_solicitacao_manutencao_status_os_id AS status,
       sm.atendimento_id

FROM mt_solicitacao_manutencao sm
INNER JOIN mt_solicitacao_manutencao_veiculo x
      ON x.mt_solicitacao_manutencao_id = sm.id
WHERE sm.concessionaria_id = 15 and sm.ativo = true AND x.ativo = TRUE

Displays the following error:

SQL Error: ERROR:  more than one row returned by a subquery used as an expression
sql database postgresql

  • What is the result of the query: SELECT DISTINCT r.cavalo FROM lg_relacao_veiculo r 
WHERE (r.cavalo = (SELECT mv.gr_veiculo_id FROM mt_solicitacao_manutencao_veiculo mv WHERE mv.mt_solicitacao_manutencao_id = sm.id AND mv.ativo = TRUE ORDER BY id DESC LIMIT 1) OR
... OR
 r.carreta2 = (SELECT mv.gr_veiculo_id FROM mt_solicitacao_manutencao_veiculo mv WHERE mv.mt_solicitacao_manutencao_id = sm.id AND mv.ativo = TRUE ORDER BY id DESC LIMIT 1) 
 ) AND r.cavalo is not null
? A single horse? (Omitted part because it exceeded the size of the comment).

  • What is happening is that your subquery is returning more than one record, in this part: WHERE v.id = ( SELECT DISTINCT r.cavalo FROM lg_relaca_veiculo r (...), it is as if you are trying to verify if 1 information is equal to a list of information (v.id = subquery that returns many things). What needs to be thought about is "should I only return a record?" if yes, Voce needs to adjust the subquery for this to occur.

1 answer

0


The problem is in this section:

WHERE v.id = (
                 SELECT DISTINCT r.cavalo FROM lg_relacao_veiculo r 
                 WHERE (
                   r.cavalo   = (SELECT mv.gr_veiculo_id FROM mt_solicitacao_manutencao_veiculo mv WHERE mv.mt_solicitacao_manutencao_id = sm.id AND mv.ativo = TRUE ORDER BY id DESC LIMIT 1) OR
                   r.carreta1 = (SELECT mv.gr_veiculo_id FROM mt_solicitacao_manutencao_veiculo mv WHERE mv.mt_solicitacao_manutencao_id = sm.id AND mv.ativo = TRUE ORDER BY id DESC LIMIT 1) OR
                   r.carreta2 = (SELECT mv.gr_veiculo_id FROM mt_solicitacao_manutencao_veiculo mv WHERE mv.mt_solicitacao_manutencao_id = sm.id AND mv.ativo = TRUE ORDER BY id DESC LIMIT 1) 
                 ) and r.cavalo is not null
          )

The Distinct Select does not solve your problem, as your select may bring more than one result line due to the OR, the Distinct only takes duplicate data, you can use the LIMIT 1 ordering downwards to solve your problem, but it is necessary to check if this is the result you really want to show.

Browser other questions tagged sql database postgresql

You are not signed in. Login or sign up in order to post.