SQL Multiple Rows in Singleton Select

Question

I have the following SQL:

WITH G1 AS 
(SELECT G.NUM_REFE, G.GUIA AS MASTER, 
        (SELECT H.GUIA FROM SAAIO_GUIAS H WHERE G.NUM_REFE = H.NUM_REFE AND H.IDE_MH ="H" AND H.CONS_GUIA="1" ) AS HOUSE
FROM SAAIO_GUIAS G WHERE G.IDE_MH ="M" AND G.CONS_GUIA ="1" )
SELECT
*
FROM G1

And it returns the error

"Multiple Rows in Singleton Select".

This is a sample of the database

Any hint will be deeply appreciated

Thanks

Answer 1

Thie problem is in CTE SELECT Subquery.

I think you can use CASE express instead of SELECT Subquery

WITH G1 AS 
(
select
  num_refe,
  Case when ide_mh = 'M' then GUIA ELSE '' END as MASTER,
  Case when ide_mh = 'H' then GUIA ELSE '' END as HOUSE
from saaio_guias
where cons_guia = 1 
)
SELECT
*
FROM G1

OR

SELECT G.NUM_REFE, G.GUIA AS MASTER,H.GUIA
FROM SAAIO_GUIAS G 
INNER JOIN 
(
    SELECT * 
    FROM SAAIO_GUIAS
    WHERE IDE_MH ='H'  AND CONS_GUIA='1'
) AS H ON  G.NUM_REFE = H.NUM_REFE
WHERE G.IDE_MH ='M' AND G.CONS_GUIA ='1'

I don't know what is your expect result.So I guess these two query might help you.

Answer 2

Your query wants to retrieve the one matching GUIA, but it seems there can be multiple entries per NUM_REFE for IDE_MH = 'H' AND CONS_GUIA = 1. Check this with

select num_refe
from saaio_guias
where ide_mh = 'H'
  and cons_guia = 1
group by num_refe
having count(*) > 1;

This should give no results, but it probably does. And if it does then it cannot work for your query and you must think about which value to pick in this case. Maybe simply the minimum or maximum:

(
  select min(h.guia)
  from saaio_guias h
  ...

Or maybe you want to delete rows from the table that you consider duplicates and add a constraint (unique index on num_refe + ide_mh + cons_guia) to prevent from such records in the future.

Your query can be written simpler using conditional aggregation by the way:

select
  num_refe,
  any_value(case when ide_mh = 'M' then guia end) as master,
  any_value(case when ide_mh = 'H' then guia end) as guia
from saaio_guias
where cons_guia = 1
group by num_refe
order by num_refe;