Thanks for the question, Leo.

Asked: August 03, 2017 - 8:27 pm UTC

Last updated: August 08, 2017 - 11:49 pm UTC

Version: 11.2.04

Viewed 10K+ times! This question is

You Asked

I need to concatenate row field into one field and I'm trying to use LISTAGG, but I need values to be distinct in the list.
I was able to do almost everything with regexp_replace as alternative, but when I have too many orders for a customer I would get issues with 4000 characters limit in LISTAGG. So I'm trying to distinct values before I use listagg. The subquery does return more then one record and I think this how I want it. I believe it suppose to work, but I get ORA-01427: single-row subquery returns more than one row

Any help would be appreciated.

SELECT 
row_number() OVER(PARTITION BY custid ORDER BY order_date_key DESC) AS row_num
,r.custid
, listagg(
 (
  SELECT DISTINCT ppo_status
  FROM fact_order f1
   INNER JOIN orderheader c1 ON f1.order_guid = c1.order_guid
   INNER JOIN customer_profile r1 ON (c1.customer_guid = r1.source_custid)
  WHERE r1.custid = r.custid
 )
 , ',') WITHIN GROUP(ORDER BY ppo_status) OVER(PARTITION BY heat_custid) 
 AS list_ppo_status
) OVER(PARTITION BY custid) 
FROM fact_order fo
 INNER JOIN orderheader c ON fo.order_guid = c.order_guid
 INNER JOIN customer_profile r ON c.customer_guid = r.source_custid 
WHERE 1=1 
AND r.custid  IN (--'29299651', '31066429', '27399282', '31066537','0001637550')
;

and Connor said...

You dont pass a list of values into LISTAGG, you allow LISTAGG to operate of set of rows in a resultset.

For example - here I try to 'pipe' a list of employee names into the first argument of listagg...and that wont work

SQL> select
  2    d.deptno,
  3    listagg(
  4      ( select distinct ename from scott.emp where deptno = d.deptno ),',')
  5      within group (order by rowid)
  6  from  scott.dept d;
  d.deptno,
  *
ERROR at line 2:
ORA-00937: not a single-group group function

If I needed to do that as per the intent above, I would have a scalar select, because that will cycle over a set of rows, that then can be listagg'd

SQL> select
  2    d.deptno,
  3    ( select
  4         listagg(ename,',') within group ( order by empno )
  5      from scott.emp
  6      where deptno = d.deptno  ) as elist
  7  from  scott.dept d;

    DEPTNO ELIST
---------- ----------------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
        40

Rating

(11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Leo Grinberg, August 04, 2017 - 12:49 pm UTC

I get your answer, but then I can't get distinct values in the list. Any help on that.

August 05, 2017 - 3:07 am UTC

Just more nesting

SQL> select
  2      d.deptno,
  3      ( select
  4           listagg(job,',') within group ( order by job )
  5        from scott.emp
  6        where deptno = d.deptno  ) as elist
  7  from  scott.dept d;

    DEPTNO ELIST
---------- ---------------------------------------------------------
        10 CLERK,MANAGER,PRESIDENT
        20 ANALYST,ANALYST,CLERK,CLERK,MANAGER
        30 CLERK,MANAGER,SALESMAN,SALESMAN,SALESMAN,SALESMAN
        40

SQL>
SQL> select
  2      d.deptno,
  3      ( select
  4           listagg(job,',') within group ( order by job )
  5        from ( select distinct job from scott.emp
  6        where deptno = d.deptno ) ) as elist
  7  from  scott.dept d;

    DEPTNO ELIST
---------- ---------------------------------------------------------
        10 CLERK,MANAGER,PRESIDENT
        20 ANALYST,CLERK,MANAGER
        30 CLERK,MANAGER,SALESMAN
        40

A reader, August 06, 2017 - 10:47 pm UTC

https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries007.htm says ...

"Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement any number of levels above the subquery."

http://docs.oracle.com/database/122/SQLRF/Using-Subqueries.htm#SQLRF52357 says ...

Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement one level above the subquery.

Why 12.2 documentation differs from a previous version while it does the opposite? I would highly appreciate if this could be clarified.

Is this a bug in 12c (I have test on 12.1 and 12.2 and they are working fine whereas the same query fails on 11.2).

select
    d.deptno,
    ( 
        select listagg(job,',') within group ( order by job ) -- level 1 nesting
        from ( 
            select distinct job  
            from scott.emp
            where deptno = d.deptno  -- referenced at level 2 nesting
        ) 
    ) as elist
from  scott.dept d

A reader, August 06, 2017 - 11:08 pm UTC

More on that ...

https://docs.oracle.com/cd/B28359_01/server.111/b28286/queries007.htm#SQLRF52340
Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement any number of levels above the subquery (p 333).

Documentation changes from any number of levels to one level from 11.2

https://docs.oracle.com/cloud/latest/db112/SQLRF/queries007.htm#SQLRF52357
Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement one level above the subquery (p 345).

A reader, August 07, 2017 - 7:24 am UTC

This is what I had to do. I was hopping there was a more elegant way to do it. I have 5 of those lists and it means 2 level deep for each list, but it works. I also made them as "temp" tables as part of the join. I don't know if your way is more efficient or mine.

Any thoughts?

A reader, August 07, 2017 - 9:16 am UTC

>>>>This is what I had to do. ...
I have 5 of those lists and it means 2 level deep for each list, but it works. ......

I was advised not to take advantage of the *bug* which (yes, works now) will break when Oracle fixes it.

This is why I am seeking clarification as to whether it is a bug?

August 07, 2017 - 8:47 pm UTC

I'd suspect its a documentation error.

In the 10g doc we said:

"a parent statement any number of levels above the subquery."

when obviously this was not true. When you went more than 1 level your query would get a syntax error.

So the documentation was ultimately fixed to reflect that it was just one level.

But since 11.2.0.4 (I think) we've improved the functionality to go deeper than one level, as my script demonstrated. So I'd suspect a documentation bug in 12.2 in the "other direction" if that makes sense.

A reader, August 07, 2017 - 9:35 pm UTC

Thank you for your response.

>>> But since 11.2.0.4 (I think) we've improved the functionality to go deeper than one level, as my script demonstrated. So I'd suspect a documentation bug in 12.2 in the "other direction" if that makes sense....

I am sure, everyone would love that to be a documentation bug than a functional bug as it helps a developer write less and get more from his/her sql.

Could you please let us know, what is the best way forward to get a confirmation that it is a documentation bug?

Thanks again.

August 08, 2017 - 11:49 pm UTC

Could you please let us know, what is the best way forward to get a confirmation that it is a documentation bug

Log a call with Support.

And there is also a feedback option within the online documentation.

A reader, August 09, 2017 - 4:06 am UTC

Thanks.

update #1

A reader, August 29, 2017 - 9:18 am UTC

Contents of the submitted ticket:

Issue: What is the maximum level of depth a parent table can be accessed by a correlated subquery?

SQL source:

WITH t (a, b) AS
(
SELECT 'M', 'N' FROM DUAL UNION ALL
SELECT 'R', 'Q' FROM DUAL UNION ALL
SELECT 'R', 'S' FROM DUAL
)
,tbl as
(
select
t.*,
(
-- level 1 nesting
select listagg (q, '') within group (order by null) from
(
-- level 2 nesting
select t.a q from dual union all
select t.b q from dual
)
) c
from t
)
select tbl.* from tbl
;

Failure:

The SQL shown below fails on 11.2.0.4.0

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Error starting at line : 2 in command -
WITH t (a, b) AS
(
SELECT 'M', 'N' FROM DUAL UNION ALL
SELECT 'R', 'Q' FROM DUAL UNION ALL
SELECT 'R', 'S' FROM DUAL
)
,tbl as
(
select
t.*,
(

select listagg (q, '') within group (order by null) from
(

select t.a q from dual union all
select t.b q from dual
)
) c
from t
)
select tbl.* from tbl
Error at Command Line : 2 Column : 1
Error report -
SQL Error: ORA-00904: "T"."A": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:

Success:

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

A B C
- - -----
M N MN
R Q QR
R S RS

Documentation:

The documentation for 12.1 says the following about the correlated subquery:

http://docs.oracle.com/database/121/SQLRF/queries007.htm#SQLRF52357

"Oracle performs a correlated subquery when a

nested subquery references a column from a table

referred to a parent statement one level above the subquery."

It is the same statement even in 12.2 documentation.
http://docs.oracle.com/database/122/SQLRF/Using-Subqueries.htm#SQLRF52357

As per the documentation, we should be getting a similar error in 12.1.0.2.0 to what has been seen in 11.2.0.4.0 test. But the query returned results.
Since there is a clear discrepancy between the documentation and the actual feature, we would like to know where is the actual bug, is it in the documentation or in the feature?

Please also see the comments from Connor McDonald from asktom.oracle.com team which highlights the issue.
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9536139800346653431

update #2 - Oracle response

A reader, August 29, 2017 - 9:20 am UTC

ORACLE RESPONSE:

Hi,

I think everything is working as per documentation.

(
select
t.*,
(
-- level 1 nesting => scalar subquery
select listagg (q, '') within group (order by null) from
(
-- level 2 nesting => inline view
select t.a q from dual union all
select t.b q from dual
)
) c
from t
)

Doc says "Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement one level above the subquery."

But the above SQL is not correlated subquery. The 1st nesting is in select clause and it is called scalar subquery and 2nd nesting is in from which is called inline view or lateral subquery.

Simply, correlated subquery should have the nesting in where clause and I dont see any nesting in where clause and so it is not qualified as correlated subquery.

A Correlated Subquery is one that is executed after the outer query is executed. So correlated subqueries take an approach opposite to that of normal subqueries. The correlated subquery execution is as follows:

-The outer query receives a row.
-For each candidate row of the outer query, the subquery (the correlated subquery) is executed once.
-The results of the correlated subquery are used to determine whether the candidate row should be part of the result set.
-The process is repeated for all rows.

But in this example, the outer query expects the rows from inner query which is executed first.

Having said, everything is working as expected and as per documentation.

Hence, the developers can make use of it at any level of nesting within the kind of subquery which was shown above as those are only inline views / scalar subquery and not correlated.

The error that happened in 11g for the same error could be due to some other bug which would have been fixed in 12.1.

update #3

A reader, August 29, 2017 - 9:27 am UTC

Our follow-up: The response from Oracle needs more clarification.

1. >> ... - Oracle: Simply, correlated subquery should have the nesting in where clause and I dont see any nesting in where clause and so it is not qualified as correlated subquery.

WITH t (a, b) AS    

(  

  SELECT 'M', 'N' FROM DUAL UNION ALL 

  SELECT 'R', 'Q' FROM DUAL UNION ALL 

  SELECT 'X', 'S' FROM DUAL  

)

,m (mm) AS    

(  

  SELECT 'N' FROM DUAL UNION ALL 

  SELECT 'Q' FROM DUAL UNION ALL 

  SELECT 'S' FROM DUAL  

)

,n (nn) AS    

(  

  SELECT 'N' FROM DUAL UNION ALL 

  SELECT 'Q' FROM DUAL UNION ALL 

  SELECT 'S' FROM DUAL  

)

,q (qq) AS    

(  

  SELECT 'N' FROM DUAL UNION ALL 

  SELECT 'Q' FROM DUAL UNION ALL 

  SELECT 'S' FROM DUAL  

)

,r (rr) AS    

(  

  SELECT 'N' FROM DUAL UNION ALL 

  SELECT 'Q' FROM DUAL UNION ALL 

  SELECT 'S' FROM DUAL  

)

select t.* from t  where b in 

(

  select mm from m  WHERE mm in 

  (

    select nn from n where nn in 

    ( 

      select qq from q where qq in 

      ( 

        select rr from r where rr  =  t.b -- t.b is referenced at 4th level

      )

    )

  )

)

;

A B

- -

M N

R Q

X S

Observation: even in the WHERE clause nesting, it does not conform to the official documentation of 12.1 and 12.2.

http://docs.oracle.com/database/121/SQLRF/queries007.htm#SQLRF52357

http://docs.oracle.com/database/122/SQLRF/Using-Subqueries.htm#SQLRF52357

2. >>... - Oracle: But in this example, the outer query expects the rows from inner query which is executed first.

The inline view (level 2 nesting) relies on the columns (t.a and t.b) of the outer query. So, it cannot be executed on its own at first.

It is demonstrated below.

WITH t (a, b) AS    

(  

  SELECT 'M', 'N' FROM DUAL UNION ALL 

  SELECT 'R', 'Q' FROM DUAL UNION ALL 

  SELECT 'R', 'S' FROM DUAL  

)

,tbl as 

(

  select 

    t.*, 

    ( 

      -- level 1 nesting 

      select listagg (q, '') within group (order by null) from 

      ( 

        -- level 2 nesting

        select t.a q from dual union all 

        select t.b q from dual  

      ) 

    ) c 

  from t

) 

select tbl.* from tbl

;

A B C

- - -----

M N MN

R Q QR

R S RS

So, our initial question remains unanswered.

update #4

A reader, August 29, 2017 - 9:32 am UTC

Final Response from Oracle for our ticket:

Hi,
...

I did some internal testing and found that the restriction for correlated subquery with respect to level of nesting has been relaxed from 12.1 onwards and hence no error happens.

This is a doc bug which has to be modified. Will raise a doc bug for that.
You can go ahead and inform the developers to use the nesting.

...

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

update #1

update #2 - Oracle response

update #3

update #4

More to Explore

Analytics

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

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

update #1

update #2 - Oracle response

update #3

update #4

More to Explore

Analytics