CODEWITHSUNDEEP
sqloracle-database
user2854333
user2854333

Reputation: 640

ListAgg and other column selection

I am trying to get the output for below query

select 
    listagg((REGEXP_SUBSTR(ABC, '[^:]+$')),';') AS Unit , (select 'XYZ' from dual) 
from 
    MNO 
where
    BATCH in (select BATCH from PQR 
              where TYPE like 'Emp'
                and ORG like 'XYZ')

It works fine if I remove (select 'XYZ' from dual), but I need both Unit as well as Org information.

The current query results in an error

ORA-00937: not a single group function

Regards

Upvotes: 0

Views: 349

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

Seems you need an aggregation for organization within a select statement containing join such as

SELECT p.org, LISTAGG((REGEXP_SUBSTR(m.abc, '[^:]+$')), ';') AS Unit           
  FROM MNO m
  JOIN PQR p 
    ON m.batch = p.batch
 WHERE p.type LIKE '%Emp%' -- you may replace with 'Emp%' or '%Emp' or 'Emp'
   AND p.org LIKE '%XYZ%' -- you may replace with 'XYZ%' or '%XYZ' or 'XYZ' 
 GROUP BY p.org

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Try selecting just the string literal 'XYZ' instead of using the subquery:

SELECT
    LISTAGG(REGEXP_SUBSTR(ABC, '[^:]+$'), ';') AS Unit,
    'XYZ'
FROM MNO m
WHERE EXISTS (SELECT 1 FROM PQR p WHERE p.BATCH = m.BATCH AND
                                        TYPE = 'Emp' AND ORG = 'XYZ');

Note: The logic of the WHERE subquery is not clear. It is not clear whether you want to find Emp anywhere in the Type column or you want an exact match. For the latter, use WHERE ... TYPE LIKE '%Emp%', and similar for ORG.

Upvotes: 1

Popeye
Popeye

Reputation: 35900

Don't use sub-query.

select listagg((REGEXP_SUBSTR(ABC, '[^:]+$')),';') AS Unit , 
       'XYZ' 
  from MNO 
 where BATCH in (select BATCH from PQR where TYPE = 'Emp' and ORG = 'XYZ')

Upvotes: 1

Related Questions