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

the first column is NUM_REFE, second GUIA, third IDE_MH and fourth one CONS_GUIA

Any hint will be deeply appreciated

Thanks

The inner select returns more than one value - since you are using it to select a single value in that position it is failing — Daniel Marcus, Mar 28 '18 at 15:04
On a sidenote: Double quotes are delimiters for names. The delimiter for string literals is the single quote. Is `CONS_GUIA` a numeric column? Then you shouldn't use quotes at all: `AND H.IDE_MH = 'H' AND H.CONS_GUIA= 1`. — Thorsten Kettner, Mar 28 '18 at 15:09
You should always tag SQL questions with the DBMS used. I suppose from the wrong usage of quotes that yours is MySQL? — Thorsten Kettner, Mar 28 '18 at 15:19
Thank you Thorsten, I change it to single quote and CONS_GUIA is not a a numeric column. I'm using Ibexpert thanks for the advise — Eduardo, Apr 02 '18 at 14:31

D-Shih · Answer 1 · 2018-04-02T15:15:59.713

0

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.

edited Apr 02 '18 at 15:15

answered Mar 28 '18 at 15:05

D-Shih

44,943
6
31
51

Thank you for your answer, but I get the error Column unknown H.NUM_REFE – Eduardo Apr 02 '18 at 14:22
@Eduardo You are welcome.you can accpt my answer if it help you :) – D-Shih Apr 04 '18 at 00:59
I mean is https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work after accepting. Mark the good answer could help people who will get the same problem can find the answer :) – D-Shih Apr 04 '18 at 02:18

Thorsten Kettner · Answer 2 · 2018-03-28T15:15:03.330

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;

Thank you, I did check and got no results which still makes me wonder why the error, as there is only one entry per NUM_REFE — Eduardo, Apr 02 '18 at 14:14

SQL Multiple Rows in Singleton Select

2 Answers2