In Oracle SQL, replace variable number of placeholders with values from a second table

Table 1 has error messages with a variable number of numbered placeholders within squiggly brackets. I need to build the completed messsage with values from a 2nd table.

Table 1:

ERROR_ID   DESCRIPTION
---------- ------------------------------------------------------------
13706      {0} {1} has an invalid time stamp format
13707      POHEADERTAB{0} {1} has an invalid date format
13708      POGENERALTAB{0} value is invalid
13709      In Line Item {0}, {1} {2} value is invalid.

Table 2

ERROR_ID   ARG_SEQ_NBR  ARG_VALUE
---------- -----------  ------------------------
13706      0            PODate
13706      1            BadData
13707      0            Due Date
13707      1            BadData
13708      0            Origin Country Code
13709      0            000001
13709      1            Actual Cost
13709      2            BadData

The relationship between the tables is, of course, the error_id and placeholder/arg_seq_nbr values.

A correct result would be, for example: PODate BadData has an invalid time stamp format.

I have tried ‘with cte…’ and a couple things but can’t quite get it to work. I can only get it to return 8 rows (instead of 4) with each of those 8 rows containing the same arg_value in each placeholder.

Answer

If you have a maximum number of arguments, then one method would be:

select replace(replace(replace(replace(t1.description, '{1}', t2.arg_1), '{2}', t2.arg_2), '{3}', t2.arg_3), '{4}', t2.arg_4)
from table1 t1 join
     (select error_id,
             max(case when ARG_SEQ_NBR = 1 then arg_value end) as arg_1,
             max(case when ARG_SEQ_NBR = 2 then arg_value end) as arg_2,
             max(case when ARG_SEQ_NBR = 3 then arg_value end) as arg_3,
             max(case when ARG_SEQ_NBR = 4 then arg_value end) as arg_4        
      from table2 t2
      group by t2.error_id
     ) t2
     using (error_id)

An alternative to this approach would be a recursive CTE. But I think the above is simpler if it works in your case.