CODEWITHSUNDEEP
sqloracle-database
Douglas Jenkins
Douglas Jenkins

Reputation: 37

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.

Upvotes: 0

Views: 297

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270533

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.

Upvotes: 3

astentx
astentx

Reputation: 6751

You may use model clause to let it do iterations for you for sequential replacement step by step.

with err(id, descr) as (
select 13706, cast(
  '{0} {1} has an invalid time stamp format'
  as varchar2(1000)) from dual union all
select 13707, 'POHEADERTAB {0} {1} has an invalid date format' from dual union all
select 13708, 'POGENERALTAB {0} value is invalid' from dual union all
select 13709, 'In Line Item {0}, {1} {2} value is invalid.' from dual
)
, vars(id, seq_num, val) as (
select 13706, 0, 'PODate' from dual union all
select 13706, 1, 'BadData' from dual union all
select 13707, 0, 'Due Date' from dual union all
select 13707, 1, 'BadData' from dual union all
select 13708, 0, 'Origin Country Code' from dual union all
select 13709, 0, '000001' from dual union all
select 13709, 1, 'Actual Cost' from dual union all
select 13709, 2, 'BadData' from dual
)
, repl as (

select *
from err
  join vars
    using(id)
model
  partition by (id)
  dimension by (seq_num)
  measures (descr, val)
  keep nav
  rules update (
    descr[seq_num >= 0] order by seq_num desc =
      /*cv()+1 access for last seq_num will
      return null, so for for this row we need
      a current value. Get previous replaced value
      for other steps.
      Desc here to get final result at seq_num = 0*/
      replace(nvl(descr[cv()+1], descr[cv()]), '{' || to_char(cv(seq_num), 'TM9') || '}', val[cv()])
  )
)
select
  id,
  descr
from repl
where seq_num = 0
ID DESCR
13707 POHEADERTAB Due Date BadData has an invalid date format
13708 POGENERALTAB Origin Country Code value is invalid
13706 PODate BadData has an invalid time stamp format
13709 In Line Item 000001, Actual Cost BadData value is invalid.

Upvotes: 2

Related Questions