Dynamic SQL using config tables

Question

I have a table which contains a list of dynamic SQL views that needs to be created

SEEDING_TABLE
-------------
KEYVALUE|VIEW_TO_BE_CREATED|FROMTABLE|NOOFCOLS
1|A|A1|3
2|B|B1|4
3|C|C1|5

The other table which contains the actual column names for the above seeding table

ORDERCOLS_FORVIEW
KEYVALUE|FROMTABLE|COLSAVAILABLE
1|A1|NUM1
1|A1|NUM2
1|A1|NUM3
2|B1|NUM1
2|B1|NUM2
2|B1|NUM3
2|B1|NUM4
3|C1|NUM1
3|C1|NUM2
3|C1|NUM3
3|C1|NUM4
3|C1|NUM5

Definition of the table FROMTABLEs as follows

A1 -> KEYVALUE|NUM1|NUM2|NUM3
B1 -> KEYVALUE|NUM1|NUM2|NUM3|NUM4
C1 -> KEYVALUE|NUM1|NUM2|NUM3|NUM4|NUM5

After doing all our logic and magic the outcome should be a dynamic SQL which should produce the below view statements

DYNAMIC_ENTRIES -> TEXT|TABLE|RANK
TEXT                                                       |TABLE | RANK
CREATE OR REPLACE VIEW A AS SELECT                         | A    | 1
KEYVALUE,                                                  | A    | 2
NUM1 AS KEY1,                                              | A    | 3
NUM1 AS NO1,                                               | A    | 4
NUM1||'|'||NUM2 AS KEY2,                                   | A    | 5
NUM2 AS NO2,                                               | A    | 6
NUM1||'|'||NUM2||'|'||NUM3 AS KEY3,                        | A    | 7
NUM3 AS NO3                                                | A    | 8
FROM A1;                                                   | A    | 9
CREATE OR REPLACE VIEW B AS SELECT                         | B    | 1
KEYVALUE,                                                  | B    | 2
NUM1 AS KEY1,                                              | B    | 3
NUM1 AS NO1,                                               | B    | 4
NUM1||'|'||NUM2 AS KEY2,                                   | B    | 5
NUM2 AS NO2,                                               | B    | 6
NUM1||'|'||NUM2||'|'||NUM3 AS KEY3,                        | B    | 7
NUM3 AS NO3,                                               | B    | 8
NUM1||'|'||NUM2||'|'||NUM3||'|'||NUM4 AS KEY4,             | B    | 9
NUM4 AS NO4                                                | B    | 10
FROM B1;                                                   | B    | 11
CREATE OR REPLACE VIEW C AS SELECT                         | C    | 1
KEYVALUE,                                                  | C    | 2
NUM1 AS KEY1,                                              | C    | 3
NUM1 AS NO1,                                               | C    | 4
NUM1||'|'||NUM2 AS KEY2,                                   | C    | 5
NUM2 AS NO2,                                               | C    | 6
NUM1||'|'||NUM2||'|'||NUM3 AS KEY3,                        | C    | 7
NUM3 AS NO3,                                               | C    | 8
NUM1||'|'||NUM2||'|'||NUM3||'|'||NUM4 AS KEY4,             | C    | 9
NUM4 AS NO4                                                | C    | 10
NUM1||'|'||NUM2||'|'||NUM3||'|'||NUM4||'|'||NUM5 AS KEY5,  | C    | 11
NUM5 AS NO5                                                | C    | 12
FROM C1;                                                   | C    | 13

Assuming for every entry in the seeding table, we have the complete list of columns available in the lookup. The logic is that for every entry in the seeding table, we need to insert entries into the final dynamic SQL table to create views for the entries in VIEW_TO_BE_CREATED column by using the FROMTABLE. For every column in the FROMTABLE that has the sequence like NUM1...NUMn they need to concatenated like a tree.

I am confused on how to approach this. We can create any number of intermediate tables or views to achieve this. Any pointers for this would be much appreciated?

Answer 1

It's a bit messy, but you could do it in plain SQL, starting with a hierarchical query to get the concatenated strings:

select keyvalue, fromtable, colsavailable, rnk,
  ltrim(sys_connect_by_path(colsavailable, '||''|''||'), '||''|''||') as path
from ordercols_forview
start with rnk = 1
connect by keyvalue = prior keyvalue
and rnk = prior rnk + 1
and prior dbms_random.value is not null
order by keyvalue, fromtable, colsavailable, rnk;

  KEYVALUE FR COLS        RNK PATH                                             
---------- -- ---- ---------- --------------------------------------------------
         1 A1 NUM1          1 NUM1                                              
         1 A1 NUM2          2 NUM1||'|'||NUM2                                   
         1 A1 NUM3          3 NUM1||'|'||NUM2||'|'||NUM3                        
         2 B1 NUM1          1 NUM1                                              
         2 B1 NUM2          2 NUM1||'|'||NUM2                                   
         2 B1 NUM3          3 NUM1||'|'||NUM2||'|'||NUM3                        
         2 B1 NUM4          4 NUM1||'|'||NUM2||'|'||NUM3||'|'||NUM4             
         3 C1 NUM1          1 NUM1                                              
         3 C1 NUM2          2 NUM1||'|'||NUM2                                   
         3 C1 NUM3          3 NUM1||'|'||NUM2||'|'||NUM3                        
         3 C1 NUM4          4 NUM1||'|'||NUM2||'|'||NUM3||'|'||NUM4             
         3 C1 NUM5          5 NUM1||'|'||NUM2||'|'||NUM3||'|'||NUM4||'|'||NUM5  

I've assumed your table really has another column you haven't shown that gives the column position. If not you can generate that somehow - maybe based on column_id for the base table column, alphabetically, or whatever. You just need a contiguous number sequence for the connect-by clause.

You can then use two unions to get the text parts for those column and path values (since they need to be separate rows in your final table), plus extra ones for the SELECT ... and FROM ... lines. Each of those needs another generated rank number. Those can be generated from the ranking in the CTE:

with ordercols_forview_cte as (
  select keyvalue, fromtable, colsavailable, rnk,
    ltrim(sys_connect_by_path(colsavailable, '||''|''||'), '||''|''||') as path
  from ordercols_forview
  start with rnk = 1
  connect by keyvalue = prior keyvalue
  and rnk = prior rnk + 1
  and prior dbms_random.value is not null
)
select 'CREATE OR REPLACE VIEW ' || s.view_to_be_created || ' AS SELECT ' as text,
  s.view_to_be_created, 1 as rnk
from seeding_table s
union all
select 'KEYVALUE,' as text,
  s.view_to_be_created, 2 as rnk
from seeding_table s
union all
select o.path || ' AS KEY' || o.rnk
  || case when o.rnk < s.noofcols then ',' end,
  s.view_to_be_created, (o.rnk * 2) + 1 as rnk
from seeding_table s
join ordercols_forview_cte o on o.keyvalue = s.keyvalue
union all
select o.colsavailable || ' AS NO' || o.rnk
  || case when o.rnk < s.noofcols then ',' end as text,
  s.view_to_be_created, (o.rnk * 2) + 2 as rnk
from seeding_table s
join ordercols_forview_cte o on o.keyvalue = s.keyvalue
union all
select 'FROM ' || o.fromtable || ';' as text,
  s.view_to_be_created, (s.noofcols * 2) + 3 as rnk
from seeding_table s
join ordercols_forview_cte o on o.keyvalue = s.keyvalue
where o.rnk = s.noofcols
order by view_to_be_created, rnk;

Which with your starting data generates:

TEXT                                                         V        RNK
------------------------------------------------------------ - ----------
CREATE OR REPLACE VIEW A AS SELECT                           A          1
KEYVALUE,                                                    A          2
NUM1 AS KEY1,                                                A          3
NUM1 AS NO1,                                                 A          4
NUM1||'|'||NUM2 AS KEY2,                                     A          5
NUM2 AS NO2,                                                 A          6
NUM1||'|'||NUM2||'|'||NUM3 AS KEY3                           A          7
NUM3 AS NO3                                                  A          8
FROM A1;                                                     A          9
CREATE OR REPLACE VIEW B AS SELECT                           B          1
KEYVALUE,                                                    B          2
NUM1 AS KEY1,                                                B          3
NUM1 AS NO1,                                                 B          4
NUM1||'|'||NUM2 AS KEY2,                                     B          5
NUM2 AS NO2,                                                 B          6
NUM1||'|'||NUM2||'|'||NUM3 AS KEY3,                          B          7
NUM3 AS NO3,                                                 B          8
NUM1||'|'||NUM2||'|'||NUM3||'|'||NUM4 AS KEY4                B          9
NUM4 AS NO4                                                  B         10
FROM B1;                                                     B         11
CREATE OR REPLACE VIEW C AS SELECT                           C          1
KEYVALUE,                                                    C          2
NUM1 AS KEY1,                                                C          3
NUM1 AS NO1,                                                 C          4
NUM1||'|'||NUM2 AS KEY2,                                     C          5
NUM2 AS NO2,                                                 C          6
NUM1||'|'||NUM2||'|'||NUM3 AS KEY3,                          C          7
NUM3 AS NO3,                                                 C          8
NUM1||'|'||NUM2||'|'||NUM3||'|'||NUM4 AS KEY4,               C          9
NUM4 AS NO4,                                                 C         10
NUM1||'|'||NUM2||'|'||NUM3||'|'||NUM4||'|'||NUM5 AS KEY5     C         11
NUM5 AS NO5                                                  C         12
FROM C1;                                                     C         13

You could vary it a bit, having another CTE with the join between seeding_table and ordercols_forview_cte and using that for the union. You can also get the paths from a recursive CTE (from Oracle 11g):

with r (keyvalue, fromtable, colsavailable, rnk, path) as (
  select keyvalue, fromtable, colsavailable, rnk, colsavailable
  from ordercols_forview
  where rnk = 1
  union all
  select ocfv.keyvalue, ocfv.fromtable, ocfv.colsavailable, ocfv.rnk,
    r.path || q'[||'|'||]' || ocfv.colsavailable
  from r
  join ordercols_forview ocfv
  on ocfv.keyvalue = r.keyvalue
  and ocfv.fromtable = r.fromtable
  and ocfv.rnk = r.rnk + 1
)
select * from r;

And can then use that instead; this does the join between that recursive CTE and the seeding table in another CTE as mentioned above, but you coudl just replace the hierarchical-query CTE with the recursive one:

with r (keyvalue, fromtable, colsavailable, rnk, path) as (
  select keyvalue, fromtable, colsavailable, rnk, colsavailable
  from ordercols_forview
  where rnk = 1
  union all
  select ocfv.keyvalue, ocfv.fromtable, ocfv.colsavailable, ocfv.rnk,
    r.path || q'[||'|'||]' || ocfv.colsavailable
  from r
  join ordercols_forview ocfv
  on ocfv.keyvalue = r.keyvalue
  and ocfv.fromtable = r.fromtable
  and ocfv.rnk = r.rnk + 1
),
combined_cte as (
  select s.keyvalue, s.view_to_be_created, s.noofcols,
    r.fromtable, r.colsavailable, r.rnk, r.path
  from seeding_table s
  join r on r.keyvalue = s.keyvalue
)
select 'CREATE OR REPLACE VIEW ' || c.view_to_be_created || ' AS SELECT ' as text,
  c.view_to_be_created, c.rnk
from combined_cte c
where c.rnk = 1
union all
select 'KEYVALUE,' as text,
  c.view_to_be_created, c.rnk + 1 as rnk
from combined_cte c
where c.rnk = 1
union all
select c.path || ' AS KEY' || c.rnk
  || case when c.rnk < c.noofcols then ',' end,
  c.view_to_be_created, (c.rnk * 2) + 1 as rnk
from combined_cte c
union all
select c.colsavailable || ' AS NO' || c.rnk
  || case when c.rnk < c.noofcols then ',' end as text,
  c.view_to_be_created, (c.rnk * 2) + 2 as rnk
from combined_cte c
union all
select 'FROM ' || c.fromtable || ';' as text,
  c.view_to_be_created, (c.noofcols * 2) + 3 as rnk
from combined_cte c
where c.rnk = c.noofcols
order by view_to_be_created, rnk;

Which gets the same result:

TEXT                                                         V        RNK
------------------------------------------------------------ - ----------
CREATE OR REPLACE VIEW A AS SELECT                           A          1
KEYVALUE,                                                    A          2
NUM1 AS KEY1,                                                A          3
NUM1 AS NO1,                                                 A          4
NUM1||'|'||NUM2 AS KEY2,                                     A          5
NUM2 AS NO2,                                                 A          6
...
NUM1||'|'||NUM2||'|'||NUM3 AS KEY3,                          C          7
NUM3 AS NO3,                                                 C          8
NUM1||'|'||NUM2||'|'||NUM3||'|'||NUM4 AS KEY4,               C          9
NUM4 AS NO4,                                                 C         10
NUM1||'|'||NUM2||'|'||NUM3||'|'||NUM4||'|'||NUM5 AS KEY5     C         11
NUM5 AS NO5                                                  C         12
FROM C1;                                                     C         13