CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user739115
user739115

Reputation: 1187

how to use listagg operator so that the query should fetch comma separated values

SELECT (SELECT STRING_VALUE 
        FROM EMP_NODE_PROPERTIES 
        WHERE NODE_ID=AN.ID ) containedWithin
FROM EMP_NODE AN 
WHERE AN.STORE_ID = ALS.ID 
AND an.TYPE_QNAME_ID=(SELECT ID 
                      FROM EMP_QNAME 
                      where LOCAL_NAME = 'document') 
AND
AND AN.UUID='13456677';

from the above query I am getting below error. ORA-01427: single-row subquery returns more than one row

so how to change the above query so that it should fetch comma separated values

Upvotes: 0

Views: 237

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You would seem to want something like this:

SELECT LISTAGG(NP.STRING_VALUE, ',') WITHIN GROUP(ORDER BY NP.STRING_VALUE) 
       as containedWithin
  FROM EMP_NODE N
  JOIN EMP_QNAME Q
    ON N.TYPE_QNAME_ID = Q.ID
  LEFT JOIN EMP_NODE_PROPERTIES NP
    ON NP.NODE_ID = N.ID
 WHERE Q.LOCAL_NAME = 'document'
   AND AN.UUID = '13456677';

This is a bit speculative because your original query would not run for the reason explained by Littlefoot.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

This query won't return error you mentioned because

  • there are two ANDs and
  • there's no ALS table (or its alias).

I suggest you post something that is correctly written, then we can discuss other errors.

Basically, it is either select string_value ... or select id ... (or even both of them) that return more than a single value.

  • The most obvious "solution" is to use select DISTINCT
  • another one is to include where rownum = 1
  • or, use aggregate functions, e.g. select max(string_value) ...

while the most appropriate option would be to join all tables involved and decide which row (value) is correct and adjust query (i.e. its WHERE clause) to make sure that desired value will be returned.

Upvotes: 1

Related Questions