I have a query like this:
select Table1.column1 AS CODE, COUNT(DINSTINCT(Table2.column1 || '|' || Table2.column2)) AS COUNT FROM Table2 INNER JOIN Table3 ON Table3.referenceColumn = Table2.referenceColumn INNER JOIN Table1 ON Table1.referenceColumn = Table2.referenceColumn WHERE .... AND Table1.column1 <> ''
The output of the query will be something like this, basically a CODE and the respective COUNT, for example:
CODE COUNT ref002 3 ref003 1
After this first query, I have a foreach
that will iterate the result of the query above. Inside the foreach
, for each result of the query
above, I want to get some information that is available in Table3, basically, I want all the values in the Table3.column1 for each CODE (Table1.Column1). So inside the foreach
I have another query to get the Table3.column for each iterated result:
select Table3.column1 FROM Table3 INNER JOIN Table3 ON Table3.referenceColumn = Table2.referenceColumn INNER JOIN Table1 ON Table1.referenceColumn = Table2.referenceColumn WHERE .... AND Table1.column1 = (equal to a parameter (Table1.column1) that is available in each foreach iteration)
And like this, I can get all the Table3.column1
values for each Table1.column1 (for each CODE) of the first query.
Doubt
Both queries are almost the same. The only difference between the query before the foreach
and the query inside the foreach is in the SELECT
part and in the WHERE
, basically the where just have an additional condition. So it doesn’t seem very good in terms of performance and maintainability has the 2 queries because both queries are almost the same. So it should be possible to get all the necessary information in the first query instead of having a second query inside the foreach
.
Do you know what is necessary to change in the first query to, besides return the CODE and COUNT, also return all values in the Table3.column1 for each CODE? So that is possible to remove the query inside the foreach and get everything that is needed only one query (the 1st query)? The necessary output for the first query should be something like:
CODE COUNT IDNUMBERS ref002 3 ab00, cd00 ref003 1 ef00
Advertisement
Answer
Your strategy generates one query per code, plus one for the initial query, so that’s indeed inefficient.
One simple option would be use a string aggregation function in the original query to generate the additional column.
In Oracle, you can use listagg()
for this:
select t1.column1 as code, count(distinct t2.column1 || '|' || t2.column2) as cnt, listagg(t3.column1) idnumbers from table2 t2 inner join table3 t3 on t3.referencecolumn = t2.referencecolumn inner join table1 t1 on t1.referencecolumn = t1.referencecolumn where ... group by t1.column1
listagg()
has various option for formating, duplicate management and sorting – you can find the details in the documentation.