0

I do not think this is relevant with subquery. But I kind of don't know well how to search this issue.

I would like to concatenate two query results, one is from a table, another is from b table. I think this is simple query, but I kind of forgot it. 

select id, value as a_value from a where id = 1 and sub_id = 1;
select id, value as b_value from b where id = 1 and sub_id = 2;

Ultimately, I would like to obtain a_value || b_value (aggregation of two values) , how could I achieve this?

The way I thought was 

select aa.a_value || bb.b_value  (
(select id, value from a where id = 1 and sub_id = 1) aa,
(select id, value from a where id = 1 and sub_id = 2) bb
)

It seems definitely wrong. Could you anyone help this out?

The point of my question was I want to some reference for each query, in the above query, aa and bb were references I wanted to gain. Actually, the real query I want to achieve is a little complicate than above.

select aa.a_value || bb.b_value (
(select id, to_clob(value) as a_value  from a where id = 1 and sub_id = 1) aa,
(select id, listagg(value, '') as b_value within group (order by sub_id) from a where id = 1 and sub_id >=2 group by id) bb);

The reason why I want to do this is that after aggregation, I run into 'ORA-01489: result of string concatenation is too long'. One of the solution can be query above. So, I would like to do that query.

Oracle version info is Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

This is the example a table has following records.

id  sub_id  value
1   1   a .... a (1000 char)
1   2   b .... b (1000 char)
1   3   c .... c (1000 char)
1   4   d .... d (1000 char)
1   5   e .... e (1000 char)

The string I want to create is a .... ab .... bc .... cd .... de .... e, aggregation of whole strings belonging to id = 1. In order to do it, I can use listagg but listadd function's return type is varchar2. After aggregation, the returned string becomes 5,000 chars so it returns error 'ORA-01489: result of string concatenation is too long' because varchar2 is limited to 4,000 chars. In order to solve this, I had to convert the first string 'a .... a' into clob type using TO_CLOB function and then I aggregate the rest of the strings ('b .... b', 'c .... c', 'd .... d', 'e .... e') into that clob, a .... a. In order to do it, I would like to create query for it.

Anna Lee
  • 909
  • 1
  • 17
  • 37
  • This really depends on the RELATION between those rows ... as you can see by the 2 answers already, they are trying to JOIN .. you need that .. however, the join conditions they gave are "guesses" ... and you need to provide additional information on how to link those 2 rows together. Depending on your data, both answers so far, may - in fact - be incorrect ... (or correct .. no idea .. yet) .. :) – Ditto Jan 05 '18 at 19:05
  • Does anyone have some solution for it? – Anna Lee Jan 05 '18 at 19:40
  • Not without some sample data posted by you: Show us some data you have ... give us table definitions with PK and indexes ... those will tell the whole story .. ;) (post that info into your original question, and not in comments .. ;) ) – Ditto Jan 05 '18 at 20:00
  • ok, so I see your update .. and I'd recommend the LISTAGG solution. Now to your issue ... the issue is the CHAR datatype .. please consider using VARCHAR2 .. always .. there is really never any good reason to use CHAR datatype. Exactly for this kind of thing. Please see these 2 links to read more on the topic: https://stackoverflow.com/questions/20417845/what-is-the-major-difference-between-varchar2-and-char and https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:123212348063, – Ditto Jan 05 '18 at 20:20

1 Answers1

1

Always state your database version - full version number, as returned by select * from v$version (for example, mine is 12.1.0.2.0).

Why does it matter? For example, in this case you could use the listagg() function - but only if your version is 11.2 or higher (not 11.1!)

select   id, listagg(value) within group (order by sub_id) as value_list
from     a
where    id = 1 and sub_id in (1, 2)
group by id -- not needed in this case; needed if you do it for more than one id

Of course, simple concatenation doesn't make sense in almost any context; you may want to separate the "values" by space or comma - you can do so by providing the second argument (optional) to listagg().

  • With listagg, there was problem so I posted this. After aggregation, more than 4000 characters come up, so I run into error 'ORA-01489: result of string concatenation is too long'. The solution is that to convert the first string into clob type and then concatenate with the rest of strings into that clob converted string. The query above represents it – Anna Lee Jan 05 '18 at 19:55
  • @AnnaLee - OK - that clarification would have been great when you posted the question originally (Now you can see why!) As an alternative that can work with CLOB, you may want to look into `XMLAGG()`. –  Jan 05 '18 at 20:20
  • But I do not want to use XMLAGG() because there was some issue with special character, also from my understanding, it converts string into xml format. I do not want it. so... – Anna Lee Jan 05 '18 at 20:23
  • @mathguy : note the update with her sample data ... might consider removing the "and sub-id in (1,2)" ;) (actually, the entire where clause, really .. hehe :) ) – Ditto Jan 05 '18 at 20:23
  • Then you may need to process this in PL/SQL, in a loop... will performance be an issue? –  Jan 05 '18 at 20:24
  • I think the query above resolves a issue clearly once it is corrected. – Anna Lee Jan 05 '18 at 20:24
  • @Ditto - no; I may remove this answer altogether, but I don't edit my answers every time a poster edits their question (in response to my answer, no less!) –  Jan 05 '18 at 20:24
  • yes, better performance is always better. Is this really need running PL/SQL. I think if I can get the references for two queries, I can get aggregate both, is it wrong? – Anna Lee Jan 05 '18 at 20:25
  • @AnnaLee - The problem, as you show, is that at least for some id's you may have five strings that must be concatenated, not two. Even worse, it's possible that the number of strings to concatenate isn't always the same. Now, functions like `greatest()` can take a variable number of arguments; but that is **not possible** for user-defined functions. An alternative would be a recursive query, but it will be much slower than an aggregate function. –  Jan 05 '18 at 20:31