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?

does this data get refreshed regularly? ie is this a batch ETL job? bring it in, transform it and make available through a view? — kevinskio, Apr 01 '16 at 13:22
May change once in a month and the config tables are taken care manually — Srini V, Apr 01 '16 at 13:24
Do you need to show as an output of select statement or you want to create these view directly .. means do you want procedures or select statement? — pratik garg, Apr 01 '16 at 13:27
Just the create view statements stored in the final table, which will be executed by another batch later — Srini V, Apr 01 '16 at 13:28

Alex Poole · Accepted Answer · 2016-04-03T17:30:20.717

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

Alex is there way to do it using ANSI Sql syntax rather than oracle specific hierarchical query syntax — Srini V, Apr 03 '16 at 09:36
@realspirituals - sure, as long as you're using Oracle 11g or higher, you can use a recursive CTE instead of a hierarchical query. Hopefully that's what you mean! I've added a version that uses that to my answer. — Alex Poole, Apr 03 '16 at 17:31
Excellent answer mate. This gave me a good starting point to dig into. — Srini V, Apr 03 '16 at 17:33
I am not using Oracle and I am using ANSI SQL and it lacks recursive CTE. In this case, what is the best alternative for generating the path? — Srini V, Apr 05 '16 at 11:12
@realspirituals - recursive CTE is ANSI, isn't it? Not sure when it was added though, or if the syntax is always quite the same (I only use Oracle). You could maybe do something with listagg (or equivalent) and analytic/window clauses but it would be messy at best; and would depend what functionality you actually have available. You might be able to do it procedurally (e.g. in Oracle a proc with a collection) but that also depends what you're allowed to do. — Alex Poole, Apr 05 '16 at 11:15
Its very old and doesnt support it,I am finding it really difficult to generate the paths without the Recursive CTE. ANd its Wx2 which lacks listagg or any other such functions — Srini V, Apr 05 '16 at 11:17
Maybe you need to ask a separate question just for that then, clearly listing the restrictions (or at least giving the product/version) so people don't waste time with things that can't work for you; and a minimal setup - the whole 'create view' part might be confusing so focus on replicating just the recursive CTE part? You can always refer back to this question for context. — Alex Poole, Apr 05 '16 at 11:25
Done, http://stackoverflow.com/questions/36425557/sql-replacement-for-recursive-cte — Srini V, Apr 05 '16 at 11:49
I'm curious why this question was tagged for Oracle; is there an Oracle back-end DB that WX2 is querying, and if so is there any scope to hide this in a procedure/function that WX2 can call? I suspect it won't understand ref cursors or pipelined tables but you never know. (Or I don't, anyway, never having used it, or even heard of it before...) — Alex Poole, Apr 05 '16 at 12:13

Dynamic SQL using config tables

1 Answers1

Linked