Subquery Returns more than 1 row even using IN statement - Oracle SQL

Question

I am working with two tables, the first, purchases, below (note, this is a clipping of the purchases table:

|    ID   |    Date   | Value | Type | Satisfied By |
|:-------:|:---------:|:-----:|:----:|:------------:|
| SALE100 |  1/1/2019 |   -5  |  OUT |              |
| SALE201 |  1/9/2019 |  -10  |  OUT |              |
| SALE203 | 2/22/2019 |   -1  |  OUT |              |
| SALE205 | 3/14/2019 |   -1  |  OUT |              |

I am trying to determine which MAKE items from another table, makes, satisfies these sales.

|    ID   |    Date    | Value | Needed For |
|:-------:|:----------:|:-----:|:----------:|
| MAKE300 | 12/24/2018 |   5   |   SALE100  |
| MAKE301 |  1/3/2019  |   3   |   SALE201  |
| MAKE399 |  1/5/2019  |   5   |   SALE201  |
| MAKE401 |  1/7/2019  |   3   |   SALE201  |
| MAKE401 |  1/7/2019  |   3   |   SALE203  |
| MAKE912 |  2/1/2019  |   1   |   SALE205  |

I am trying to write a query that will enable me to determine which ID or IDs from the makes table satisfies my sales.

My end results would look either like, in the case that they are LISTAGG:

|    ID   |    Date   | Value | Type |        Satisfied By       |
|:-------:|:---------:|:-----:|:----:|:-------------------------:|
| SALE100 |  1/1/2019 |   -5  |  OUT |          MAKE300          |
| SALE201 |  1/9/2019 |  -10  |  OUT | MAKE301, MAKE399, MAKE401 |
| SALE203 | 2/22/2019 |   -1  |  OUT |          MAKE401          |
| SALE205 | 3/14/2019 |   -1  |  OUT |          MAKE912          |

However, when writing the following line of code:

(SELECT LISTAGG(makes.id, ', ') WITHIN GROUP (ORDER BY NULL) FROM makes WHERE purchased.id = needed_for.id) ELSE NULL END AS Satisfied_By

results in an error stating:

ORA-01489: result of string concatenation is too long 01489. 00000 - "result of string concatenation is too long"

I have also tried the following query to obtain results like this (which is ideal):

|    ID   |    Date   | Value | Type | Satisfied By |
|:-------:|:---------:|:-----:|:----:|:------------:|
| SALE100 |  1/1/2019 |   -5  |  OUT |    MAKE300   |
| SALE201 |  1/9/2019 |  -10  |  OUT |    MAKE301   |
| SALE201 |  1/9/2019 |  -10  |  OUT |    MAKE399   |
| SALE201 |  1/9/2019 |  -10  |  OUT |    MAKE401   |
| SALE203 | 2/22/2019 |   -1  |  OUT |    MAKE401   |
| SALE205 | 3/14/2019 |   -1  |  OUT |    MAKE912   |

CASE WHEN Type = 'OUT' THEN
(SELECT 
makes.id

FROM
makes
WHERE
makes.id IN (

  SELECT
    makes.id

  FROM
    makes

  WHERE
    sales.id = purchases.id

)) ELSE NULL END AS Satisfied_By

Which yields

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

I have found many examples of this error on Stack Overflow, which is where I adopted the IN method from, and from this source, but I am still getting the error. Any help is appreciated.

Answer 1

Your 'ideal' result is a simple join:

select p.id, p.dt, p.value, p.type, m.id as satisfied_by
from purchases p
join makes m on m.needed_for = p.id;

You might want to make it a left join in case there are not matches, if that is possible in your data.

Quick demo with your data:

-- CTEs for sample data
with purchases (id, dt, value, type, satisfied_by) as (
            select 'SALE100', date '2019-01-01', -5, 'OUT', null from dual
  union all select 'SALE201', date '2019-01-09', -10, 'OUT', null from dual
  union all select 'SALE203', date '2019-02-22', -1, 'OUT', null from dual
  union all select 'SALE205', date '2019-03-14', -1, 'OUT', null from dual
),
makes (id, dt, value, needed_for) as (
            select 'MAKE300', date '2018-12-24', 5, 'SALE100' from dual
  union all select 'MAKE301', date '2019-01-03', 3, 'SALE201' from dual
  union all select 'MAKE399', date '2019-01-05', 5, 'SALE201' from dual
  union all select 'MAKE401', date '2019-01-07', 3, 'SALE201' from dual
  union all select 'MAKE401', date '2019-01-07', 3, 'SALE203' from dual
  union all select 'MAKE912', date '2019-02-01', 1, 'SALE205' from dual
)
-- actual query
select p.id, p.dt, p.value, p.type, m.id as satisfied_by
from purchases p
left join makes m on m.needed_for = p.id;

ID      DT              VALUE TYP SATISFIED_BY                  
------- ---------- ---------- --- ------------------------------
SALE100 2019-01-01         -5 OUT MAKE300                       
SALE201 2019-01-09        -10 OUT MAKE301                       
SALE201 2019-01-09        -10 OUT MAKE399                       
SALE201 2019-01-09        -10 OUT MAKE401                       
SALE203 2019-02-22         -1 OUT MAKE401                       
SALE205 2019-03-14         -1 OUT MAKE912                       

The listagg version is also fairly simple:

select p.id, p.dt, p.value, p.type,
  listagg(m.id, ', ') within group (order by m.id) as satisfied_by
from purchases p
left join makes m on m.needed_for = p.id
group by p.id, p.dt, p.value, p.type;

ID      DT              VALUE TYP SATISFIED_BY                  
------- ---------- ---------- --- ------------------------------
SALE100 2019-01-01         -5 OUT MAKE300                       
SALE201 2019-01-09        -10 OUT MAKE301, MAKE399, MAKE401     
SALE203 2019-02-22         -1 OUT MAKE401                       
SALE205 2019-03-14         -1 OUT MAKE912                       

It isn't clear from your code fragment quite what you are doing wrong, but it looks like you are correlating your subqueries properly; but then you don't really need them... And if you are already correlating the listagg version's subquery properly then you might just actually have too many matches in you real data; it's either that or the subquery is returning more data than it should and aggregating all of that breaks the size limit.

---

The "missing" part of the subquery is that I use a CASE WHEN TYPE = 'OUT' THEN, so nothing fancy, but that would limit the amount of records I have

You could maybe include that in the join condition:

from purchases p
left join makes m on (p.type = 'OUT' and m.needed_for = p.id)

---

You can still use a subquery for the listagg approach:

select p.id, p.dt, p.value, p.type,
  (
    select listagg(m.id, ', ') within group (order by m.id) 
    from makes m
    where m.needed_for = p.id
    -- and p.type = 'OUT'
  ) as satisfied_by
from purchases p;

which may in fact be what you are doing - it isn't entirely clear if that condition is equivalent to the purchased.id = needed_for.id you showed. If you still get ORA-01489 from that then you will from the non-subquery version too, and you just have too many matches to fit the aggregated list into 4000 bytes. And if they both work then I'm not sure what the advantage of having a subquery would be - at best the Oracle optimiser might make them equivalent, but it seems more likely that performance would be worse. You'd need to test with your real environment and data to be sure though.

The non-listagg subquery won't work, with or without the in() (which just adds another level of subquery with no real effect):

select p.id, p.dt, p.value, p.type,
  (
    select m.id
    from makes m
    where m.needed_for = p.id
    -- and p.type = 'OUT'
  ) as satisfied_by
from purchases p;

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

... because you know and expect you will get multiple rows from that subquery, at least for some purchases. With your sample data this does actually work if you exclude SALE201, but that isn't helpful. You're trying to cram multiple values into a single scalar result, which won't work, and is why you needed to look at listagg in the first place.

As well as the xmlagg variant demonstrated by @Tejash, you could also get the combined values as a collection, e.g.:

select p.id, p.dt, p.value, p.type,
  cast(multiset(
    select m.id
    from makes m
    where m.needed_for = p.id
    -- and p.type = 'OUT'
  ) as sys.odcivarchar2list) as satisfied_by
from purchases p;

ID      DT              VALUE TYP SATISFIED_BY                                      
------- ---------- ---------- --- --------------------------------------------------
SALE100 2019-01-01         -5 OUT ODCIVARCHAR2LIST('MAKE300')                       
SALE201 2019-01-09        -10 OUT ODCIVARCHAR2LIST('MAKE301', 'MAKE399', 'MAKE401') 
SALE203 2019-02-22         -1 OUT ODCIVARCHAR2LIST('MAKE401')                       
SALE205 2019-03-14         -1 OUT ODCIVARCHAR2LIST('MAKE912')                       

... or as a table-type collection of you have one defined in your schema. That may be even harder to work with though, and even further from your 'ideal' output. It depends a bit on what will consume your result set, and how.

Answer 2

Your first query returned the following error:

ORA-01489: result of string concatenation is too long 01489. 00000 - "result of string concatenation is too long"

As concatenation in the column "Satisfied_By" becomes more than 4000 characters long. You need to use XMLAGG for the safer side while concatenating the VARCHAR column.

You can try the following query:

-- DATA PREPARATION
with purchases (id, dt, value, type, satisfied_by) as (
  select 'SALE100', date '2019-01-01', -5, 'OUT', null from dual
  union all select 'SALE201', date '2019-01-09', -10, 'OUT', null from dual
  union all select 'SALE203', date '2019-02-22', -1, 'OUT', null from dual
  union all select 'SALE205', date '2019-03-14', -1, 'OUT', null from dual
),
makes (id, dt, value, needed_for) as (
  select 'MAKE300', date '2018-12-24', 5, 'SALE100' from dual
  union all select 'MAKE301', date '2019-01-03', 3, 'SALE201' from dual
  union all select 'MAKE399', date '2019-01-05', 5, 'SALE201' from dual
  union all select 'MAKE401', date '2019-01-07', 3, 'SALE201' from dual
  union all select 'MAKE401', date '2019-01-07', 3, 'SALE203' from dual
  union all select 'MAKE912', date '2019-02-01', 1, 'SALE205' from dual
)
-- actual query
SELECT
    P.ID,
    P.DT,
    P.VALUE,
    P.TYPE,
    RTRIM(XMLAGG(XMLELEMENT(E, M.ID, ',').EXTRACT('//text()')
        ORDER BY
            M.ID
    ).GETCLOBVAL(), ',') AS SATISFIED_BY
FROM
    PURCHASES P
    LEFT JOIN MAKES M ON P.ID = M.NEEDED_FOR
GROUP BY
    P.ID,
    P.DT,
    P.VALUE,
    P.TYPE;

DB Fiddle demo

Cheers!!