-2

I have 2 tables that I am trying to extract info from it. This is a sample of the tables below as these are large tables. I want the description ON TABLE 1 to be on a column KEY_EVENT BY LOOKING into table 2 and the only common column is an APP column. but error has been coming subquery can not return more than one row.

TABLE 1

 APP    LANGUAGE    DESCRIPTION 
195          12         Involved person 
195          27       Involved person   
 196           1         Involvert legemiddel   
196           2    Involved drug    
196           3    Involverad lakemedel 
196          4     Involveret l?gemiddel    
196          12    Involved drug    
196          27    Involved drug    
197          1     Eksponeringsverdier  
197          2     Exposure values  
197          3     Exponeringsvarden    
197          4     Eksponeringsv?rdier  
197         12     Exposure values  
197         27     Exposure values  
198          1     Indikatorer  
198          2     Indicators   
198          3     Indikatorer  
198          4     Indikatorer  
198         12     Indicators   
198         27     Indicators   
199         1      Generell klassifisering  
199         2      General classification   
199         3      Generell klassificering  
199         4      Generel klassifisering   
199        12      General classification   
199         27     General classification   


         TABLE 2
 TRANS    APP
 1         195
 2         195
 3         196
 4         196
 5         196
 6         196
 7         196
 8         196
 9         197
 10        197
11         197
12         197
13         197
14         197
15         198
16         198
17         198
18         198
19         198
20         198
21         199
22         199
23         199
24         199
25         199
26         199

In addition, I want to use - I want to concatenate the DESCRIPTION USING LISTAGG if we have common appl with multiple description

     SELECT SELECT  NVL(TO_CHAR(TRANS.ID), 'NULL') AS ID
       , 'HEADER'

       ,NVL(TO_CHAR((SELECT L_APP.DESCRIPTION  
  FROM  L_APP L_APP



      WHERE  EXISTS  ( SELECT 1 FROM TRANS WHERE L_APP.APP =                  TRANS.APP AND LANGUAGE = 2


      )

         )), 'NULL') AS KEY_EVENT
great77
  • 143
  • 4
  • 20
  • 1
    A read the question several times but I still can't figure out what you actually want. Could you please add the input and corresponding desired output? – Robert Kock Feb 12 '19 at 13:31
  • I want to get value description based on the common column APP – great77 Feb 12 '19 at 14:01

3 Answers3

1

You need to use a aggregation to combine rows. You cant show all rows in one cell. So, LISTAGG is what you are looking for to concenate strings.

Here is an example query:

SELECT  
NVL(TO_CHAR(TRANS.ID), 'NULL') AS ID, 
'HEADER',
NVL(
    TO_CHAR(
            (
                SELECT LISTAGG ( L_APP.DESCRIPTION ,',' ) 
                FROM  L_APP 
                WHERE  EXISTS  ( 
                                SELECT 1 
                                FROM TRANS 
                                WHERE L_APP.APP =                  TRANS.APP AND LANGUAGE = 2
                            )

           )
        ), 'NULL'
) AS KEY_EVENT
Tufan Barış Yıldırım
  • 3,952
  • 26
  • 36
1

From what I understood you need description in English (language = 2) as column KEY_EVENT and also a list of all existing descriptions in other languages. Please look at this syntax. I also attached a demo which shows how this query works with data which you provided.

dbfiddle demo

select trans, t2.app, 
       nvl(max(case when language = 2 then description end), 'NULL') key_event,
       listagg(description, ', ') within group (order by t1.language) list
  from table2 t2 
  left join table1 t1 on t1.app = t2.app 
  group by trans, t2.app
  order by trans;

Looks like 12 and 27 are also English descriptions, if you want to use them (in case 2 is absent) run this:

select trans, t2.app,  
       coalesce(max(case when language =  2 then description end), 
                max(case when language = 12 then description end), 
                max(case when language = 27 then description end), 
               'NULL') key_event,
       listagg(description, ', ') within group (order by t1.language) list
  from table2 t2 
  left join table1 t1 on t1.app = t2.app 
  group by trans, t2.app
  order by trans;
Ponder Stibbons
  • 14,723
  • 2
  • 21
  • 24
  • Thank you Ponder. I am using the first one. the query is saying that I have many values in this line listagg(description, ', ') within group (order by t1.language) list – great77 Feb 12 '19 at 15:26
  • If you mean _result of string concatenation is too long_ then you can't use `listagg()`, or you have to use one of the solutions from [this post](https://stackoverflow.com/questions/13795220/listagg-function-result-of-string-concatenation-is-too-long). – Ponder Stibbons Feb 12 '19 at 15:48
  • Ponder, It is not the result of string concatenation. The line is complaining is actually too many values is nvl(max(case when language = 2 then description end), 'NULL') key_event, – great77 Feb 12 '19 at 16:14
  • Sorry, but I don't understand. I showed you solution, I showed you **working** dbfiddle with your own data. Please read [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve). – Ponder Stibbons Feb 12 '19 at 16:22
  • It is working, but is creating duplicate and also could not easily fit in with other code I have – great77 Feb 14 '19 at 22:59
0

I have been able to use this answer in this page and itt has not created any duplicate during the concatenation into one the filed:

subquery eror and too many values using xmllagg

The final code is 

  select app, key_event,  xmlquery('distinct-values(//text())' passing xmldoc returning content).getclobVal()
from (
select  t2.app,  
       coalesce(max(case when language =  2 then description end), 
                max(case when language = 12 then description end), 
                max(case when language = 27 then description end), 
               'NULL') key_event,
         XMLELEMENT(root,xmlagg(XMLELEMENT(e,description,','))
                  ) xmldoc
  from table2 t2 
  left join table1 t1 on t1.app = t2.app 
  group by trans, t2.app
   order by trans);
great77
  • 143
  • 4
  • 20