how to join multiple select statement with full outer join. Error: subquery is returning more than one row

Question

I have this code have been battling with since yesterday, when I unit test each part of this code, it is working, but I need to put them together to generate one output result. This is the full code below : but is giving subquery is returning more than one row.

SELECT NVL(TO_CHAR(D_TRANS.TRANS), 'NULL') AS ID, 'HEADER', D_SPILL.status, 
      (SELECT L_APPLICATION.APPLICATION 
         FROM L_APPLICATION   L_APPLICATION 
        WHERE LANGUAGE = 2 AND APPLICATION = D_TRANS.APPLICATION) 
      AS CASE_TYPE, 
      NVL(TO_CHAR(D_TRANS.UNIT_IN_CHARGE), 'NULL') AS UNIT_IN_CHARGE,
      NVL(TO_CHAR(D_TRANS.PERSON_IN_CHARGE), 'NULL') AS PERSON_IN_CHARGE,
      NVL(TO_CHAR(D_TRANS.STATUS), 'NULL') AS CASE_STATUS, 
      NVL(TO_CHAR(D_TRANS.DEADLINE), 'NULL') AS INTERNAL_DEADLINE,
            ( select   xmlquery('distinct-values(//text())' passing xmldoc returning content).getclobVal()
       FROM  ( select  d_synergi_category.trans, 
       coalesce(max(case when language =  2 then description end), 'NULL'), 
         XMLELEMENT(root,xmlagg(XMLELEMENT(e,description,','))
                  ) xmldoc
           from L_CASE_CATEGORY  
         LEFT JOIN  d_synergi_category ON  d_synergi_category.case_category          = L_CASE_CATEGORY.case_category 
         group by  d_synergi_category.trans

             )
                      )              
                  FROM D_TRANS
             FULL OUTER  JOIN D_SPILL
              ON D_TRANS.TRANS=D_SPILL.TRANS
               ORDER BY D_TRANS.TRANS DESC;

               If I remove the part code  below with xmltagg and test both parts of the code separately it is working.

First part working separately

  ( select   xmlquery('distinct-values(//text())' passing xmldoc returning content).getclobVal()
       FROM  ( select  d_synergi_category.trans, 
       coalesce(max(case when language =  2 then description end), 'NULL'), 
         XMLELEMENT(root,xmlagg(XMLELEMENT(e,description,','))
                  ) xmldoc
  from L_CASE_CATEGORY  
     LEFT JOIN  d_synergi_category ON  d_synergi_category.case_category =         L_CASE_CATEGORY.case_category 
  group by  d_synergi_category.trans

) )

Second part working separately is :

SELECT NVL(TO_CHAR(D_TRANS.TRANS), 'NULL') AS ID, 'HEADER',D_SPILL.status, 
   (SELECT L_APPLICATION.APPLICATION FROM L_APPLICATION 
    WHERE L_APPLICATION WHERE LANGUAGE = 2 
     AND APPLICATION = D_TRANS.APPLICATION) AS CASE_TYPE , 
    NVL(TO_CHAR(D_TRANS.UNIT_IN_CHARGE), 'NULL') AS UNIT_IN_CHARGE, 
    NVL(TO_CHAR(D_TRANS.PERSON_IN_CHARGE), 'NULL') AS PERSON_IN_CHARGE, 
    NVL(TO_CHAR(D_TRANS.STATUS), 'NULL') AS CASE_STATUS , 
    NVL(TO_CHAR(D_TRANS.DEADLINE), 'NULL') AS INTERNAL_DEADLINE
  FROM D_TRANS
  FULL OUTER  JOIN D_SPILL
       ON D_TRANS.TRANS=D_SPILL.TRANS
   ORDER BY D_TRANS.TRANS DESC;

Do you use `XMLAGG` to emulate a `LISTAGG`? Are you working with such an old Oracle version that `LISTAGG` is not available? Please tag your request with the Oracle version you are using. It would also help to show sample data, some rows the one of the separate queries return, some rows the other query returns, and the rows the combined query shall return. — Thorsten Kettner, Feb 14 '19 at 07:01

Littlefoot · Answer 1 · 2019-02-14T06:10:02.943

1

Query (SELECT rtrim(xmlagg( ... must return exactly one row.

We don't have your tables nor data, but it seems that you didn't join its tables (L_CASE_CATEGORY, D_SYNERGI_CATEGORY) with any of tables contained in main query's FROM clause (TRANS, D_SPILL). I suggest you do that and see what happens.

[EDIT]

This is what I meant:

select nvl(to_char(d_trans.trans, 'null') as id,
       ...,
       rtrim(xmlagg(xmlelement(...)) as some_name          --> XLM stuff goes here
  from l_case_category left join d_synergi_category on ...
       join l_case_category on ...                         --> XML subquery's tables go here,
                                                           --> properly joined to other tables

edited Feb 14 '19 at 06:10

answered Feb 13 '19 at 21:42

Littlefoot

131,892
15
35
57

I did join the d_synergi_category sorry i should have put it in the sample but the L_case_category does not have Trans which is the common ID. Could that be the problem since L_case_category does not have Common Trans as ID. – great77 Feb 13 '19 at 21:49
1

Generally speaking, unless you know what you're doing, you shouldn't use correlated subqueries. If it is impossible to make that SELECT return exactly one row, don't use it - move its tables into main query's FROM clause and properly join everything. Then check the result and - if you aren't satisfied with it - see what can be done to fix it. On the other hand, there is a workaround (probably quite stupid so I'm not sure whether I should propose it at all): if you use `WHERE ROWNUM = 1` in subquery, it'll work. Or even `SELECT MAX(rtrim(xmlagg(...`. As I said - probably not a very good idea. – Littlefoot Feb 13 '19 at 22:03
Thanks. I do not understand that part you said move tables to main Query from clause. The column am trying to generate will have to be in the order as specified in the requirement. – great77 Feb 14 '19 at 05:05
I edited my message and put a piece of code which, I hope, shows what I meant. Have a look, please. – Littlefoot Feb 14 '19 at 06:10

score 0 · Answer 2 · answered Feb 13 '19 at 21:50

What @Littlefoot said, but I'm going to take a guess at how your tables might be joined, so that you have an example.

SELECT NVL(TO_CHAR(D_TRANS.TRANS), 'NULL') AS ID, 'HEADER',D_SPILL.status, 
   (SELECT L_APPLICATION.APPLICATION FROM L_APPLICATION 
    WHERE L_APPLICATION WHERE LANGUAGE = 2 
     AND APPLICATION = D_TRANS.APPLICATION) AS CASE_TYPE , 
    NVL(TO_CHAR(D_TRANS.UNIT_IN_CHARGE), 'NULL') AS UNIT_IN_CHARGE, 
    NVL(TO_CHAR(D_TRANS.PERSON_IN_CHARGE), 'NULL') AS PERSON_IN_CHARGE, 
    NVL(TO_CHAR(D_TRANS.STATUS), 'NULL') AS CASE_STATUS , 
    NVL(TO_CHAR(D_TRANS.DEADLINE), 'NULL') AS INTERNAL_DEADLINE,
    (SELECT  rtrim(xmlagg( 
             XMLELEMENT(e,L_CASE_CATEGORY.DESCRIPTION,',').EXTRACT('//text()')
              ).GetClobVal(),',') 
     FROM L_CASE_CATEGORY
     INNER JOIN   D_SYNERGI_CATEGORY  on  -- changed
           D_SYNERGI_CATEGORY.CASE_CATEGORY = L_CASE_CATEGORY.CASE_CATEGORY   
         AND L_CASE_CATEGORY.LANGUAGE = 2 
     WHERE d_synergi_category.trans = D_TRANS.TRANS -- added this line
     GROUP BY  D_SYNERGI_CATEGORY.CASE_CATEGORY, d_synergi_category.trans) AS CAT_DESC_LIST
  FROM D_TRANS
  FULL OUTER  JOIN D_SPILL
       ON D_TRANS.TRANS=D_SPILL.TRANS
   ORDER BY D_TRANS.TRANS DESC;

Edit: updated from your comment. This would be a lot easier if you provided table structure and example data.

score 0 · Answer 3 · answered Feb 14 '19 at 06:55

Your subquery SELECT rtrim(xmlagg(... returns one row per CASE_CATEGORY and trans, because of:

GROUP BY D_SYNERGI_CATEGORY.CASE_CATEGORY, d_synergi_category.trans

When you put it in the main query's select clause, however, you don't want it to return all those rows per main query row, but the one row matching the main query row (just like you find the matching application row with WHERE APPLICATION = D_TRANS.APPLICATION).

So remove the GROUP BY clause and replace it with the WHERE clause instead. Something like

(
  SELECT
    RTRIM(XMLAGG( 
       XMLELEMENT(e, cc.description,',').EXTRACT('//text()')
    ).GetClobVal(),',') 
  FROM l_case_category cc
  JOIN d_synergi_category sc ON sc.case_category = cc.case_category   
  WHERE sc.case_category = d_spill.case_category -- <=== here
    AND sc.trans = d_trans.trans -- <=== and here
    and cc.language = 2
)

(Just replace my criteria with your real criteria. Only you know how the tables are related. It is hard for me to even figure it out what the query is supposed to return. I don't understand why you full outer join with D_SPILL in one of the separate queries, without using any of its columns in the select clause. I don't know either why you outer join D_SYNERGI_CATEGORY on L_CASE_CATEGORY.LANGUAGE = 2 in the other query. This seems strange.)

Check this link. https://stackoverflow.com/questions/54666218/subquery-eror-and-too-many-values-using-xmllagg?noredirect=1#comment96124524_54666218 The problem is that the code in that link is working but when I put it insde the other code to keep joining the column for output, it is coming with error. The group by is neccessary to concatenate. — great77, Feb 14 '19 at 07:05
The `GROUP BY` is only necessary for the stand-alone query, because you want it to return one row per case_category and trans. It must be replaced with a `WHERE` clause when used as a subquery in the select clause, because you no longer want it to return one row *per* case_category and trans, but the one row for *one* given case_category and trans. — Thorsten Kettner, Feb 14 '19 at 07:15
Well, seing the query's result, you seem to want a row with many descriptions in case there is no match. If so, this becomes much more complicated and you'll have to join somehow instead. I can imagine that all this is less complicated than it seems. You should have made a request showing the mere tables with sample data and the expected result with an explanation. — Thorsten Kettner, Feb 14 '19 at 07:38

how to join multiple select statement with full outer join. Error: subquery is returning more than one row

3 Answers3