Improve query performance and maintainability

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

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.