Adding columns to a table depending on a configuration in Oracle SQL

Question

I want to build a table based on a configuration that tells me which column add from which other table in my database with which join condition. To be more clear, I have the following setup:

My input table (call it INPUT) which has just two fields: KEY1 and KEY2. Each of them represents an independent key (there are no duplicates) and not a joint key;
A configuration (call it CONF), made of the following columns:

SOURCE
FIELD
KEY
LABEL

The SOURCE column is the name of an existing table in my db that contains either the field KEY1 or the field KEY2. FIELD is the name of a column inside SOURCE. KEY can assume values KEY1 or KEY2 depending on how to join with the INPUT table. Finally, LABEL is an arbitrary name. The CONF table is made of ~20 rows.

For each row, I would like to add a column to the INPUT table based on the row specifics. In pseudo code, this would be something like:

SELECT INPUT.*, {SOURCE}.{FIELD} AS {LABEL}
FROM INPUT
LEFT JOIN {SOURCE} ON INPUT.{KEY} = {SOURCE}.{KEY}

Iterating the above, eventually you end up with a table of 2 + the number of rows of the CONF table and the same rows of the INPUT. The above, at least for me, it's pretty obvious to achieve with a programming language. I have a python application with a remote db server and I could loop in python and collecting partial results. However, that would imply a lot of network traffic and a big memory usage for the application (the oracle db server is way bigger resource wise). So, the ideal would be to let the db create this table and then fetch a chunk in python, postprocess it and then go to the next chunk.

Other important elements:

Our db connection has only select, insert and update privileges. We cannot create new tables.
The CONF table might vary and the user of the application can add new rows to it (which will translate in new columns for the output).
The INPUT table has about 1.5 millions rows.

It's possible to do the above entirely in oracle sql?

Not in plain SQL, no, but certainly with dynamic SQL using a bit of PL/SQL, yes. SQL by itself cannot programmatically reference object names (tables, columns), only values. The moment you want to abstract object names you have to use dynamic SQL. — Paul W
– Paul W, Commented Mar 25, 2023 at 16:56
What version of Oracle are you running? With full dot notation (select version_full from v$instance). If that errors select just the version column. — Paul W
– Paul W, Commented Mar 26, 2023 at 21:27
How do you know which SOURCE table has the key value in question? I'm thinking through your design and can't see how you can avoid visiting every single SOURCE defined in CONF while looking for the key values. If that's the case, then you might as well just prejoin every possible SOURCE in a single SQL adn use DECODE/CASE to pick out the matching row. — Paul W
– Paul W, Commented Mar 26, 2023 at 23:51

d r · Accepted Answer · 2023-03-27 15:41:06Z

Your instruction
LEFT JOIN {SOURCE} ON INPUT.{KEY} = {SOURCE}.{KEY}
implicates that existing tables that you want to join have the same columns as INPUT TABLE - KEY_1 and/or KEY_2.
I created two such tables ET_1 and ET_2:

Select * From ET_1;
FLD_1                FLD_2                KEY_1                KEY_2               
-------------------- -------------------- -------------------- --------------------
ABC                  DEF                  G                    H                  

Select * From ET_2;
COL_1                COL_2                KEY_2               
-------------------- -------------------- --------------------
PQR                  STU                  D

... INPUT_TBL and CONF_TBL for testing purposes...

Select * From INPUT_TBL;
KEY_1                KEY_2               
-------------------- --------------------
A                    B                   
C                    D                   
E                    F                   
G                    H                   
I                    J                   
K                    L                  

Select * From CONF_TBL;
SOURCE               FIELD                KEY                  LABEL               
-------------------- -------------------- -------------------- --------------------
ET_1                 FLD_1                KEY_1                LBL_1               
ET_1                 FLD_2                KEY_2                LBL_2               
ET_2                 COL_1                KEY_2                LBL_3               
ET_2                 COL_2                KEY_2                LBL_4

The sql code that you asked for could be generated dynamicaly using PL/SQL:

SET SERVEROUTPUT ON
Declare
    CURSOR c IS Select "SOURCE", "FIELD", "KEY", "LABEL" From CONF_TBL;
    mSource   VarChar2(20);
    mField    VarChar2(20);
    mKey      VarChar2(20);
    mLabel    VarChar2(20);
    mSQL_sel  VarChar2(1000) := 'Select i.*, ';
    mSQL_from VarChar2(1000) := 'From INPUT_TBL i ';
    mSQL_join VarChar2(1000);
    mSQL      VarChar2(4000);
    cnt       Number  := 0;
    ta        VarChar2(32);
Begin
    OPEN c;
    LOOP
        Fetch c Into mSource, mField, mKey, mLabel;
        Exit When c%NOTFOUND;
        cnt := cnt + 1;
        ta := 'ta' || cnt;
        mSQL_sel := mSQL_sel || ta || '.' || mField || ' "' || Nvl(mLabel, ta || '_' || mField) || '", ';
        mSQL_join := mSQL_join || 'LEFT JOIN ' || mSource || ' ' || ta || ' ON(' || ta || '.' || mKey || ' = i.' || mKey || ')' || Chr(10);

    END LOOP;
    CLOSE c;
    mSQL_sel := SubStr(mSQL_sel, 1, Length(mSQL_sel) -2);
    mSQL_join := SubStr(mSQL_join, 1, Length(mSQL_join) -1);
    mSQL := mSQL_sel || Chr(10) || mSQL_from || Chr(10) || mSQL_join;
    DBMS_OUTPUT.PUT_LINE(mSQL);
End;
/

Resulting (mSQL) code could then be used as regular sql or as refcursor or anything else .... Below is generated code and result against the tables described above

Select i.*, ta1.FLD_1 "LBL_1", ta2.FLD_2 "LBL_2", ta3.COL_1 "LBL_3", ta4.COL_2 "LBL_4"
From INPUT_TBL i 
LEFT JOIN ET_1 ta1 ON(ta1.KEY_1 = i.KEY_1)
LEFT JOIN ET_1 ta2 ON(ta2.KEY_2 = i.KEY_2)
LEFT JOIN ET_2 ta3 ON(ta3.KEY_2 = i.KEY_2)
LEFT JOIN ET_2 ta4 ON(ta4.KEY_2 = i.KEY_2)

PL/SQL procedure successfully completed.


KEY_1                KEY_2                LBL_1                LBL_2                LBL_3                LBL_4               
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
C                    D                                                              PQR                  STU                 
A                    B                                                                                                       
G                    H                    ABC                  DEF                                                           
E                    F                                                                                                       
K                    L                                                                                                       
I                    J                                                                                                       

6 rows selected.

If there are no labels defined (optional) in table CONF_TBL then the result would be like here:

Select i.*, ta1.FLD_1 "ta1_FLD_1", ta2.FLD_2 "ta2_FLD_2", ta3.COL_1 "ta3_COL_1", ta4.COL_2 "ta4_COL_2"
From INPUT_TBL i 
LEFT JOIN ET_1 ta1 ON(ta1.KEY_1 = i.KEY_1)
LEFT JOIN ET_1 ta2 ON(ta2.KEY_2 = i.KEY_2)
LEFT JOIN ET_2 ta3 ON(ta3.KEY_2 = i.KEY_2)
LEFT JOIN ET_2 ta4 ON(ta4.KEY_2 = i.KEY_2)

PL/SQL procedure successfully completed.

KEY_1                KEY_2                ta1_FLD_1            ta2_FLD_2            ta3_COL_1            ta4_COL_2           
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
C                    D                                                              PQR                  STU                 
A                    B                                                                                                       
G                    H                    ABC                  DEF                                                           
E                    F                                                                                                       
K                    L                                                                                                       
I                    J                                                                                                       

6 rows selected.

U P D A T E
If you want to avoid possible duplication of the same joins you could change the cursor to generate the fields (of duplicated joins) in the same row.

    CURSOR c IS 
              Select "SOURCE", "FIELD" as FIELD_1, 
                  CASE  WHEN CNT = 2 THEN 'xxx'
                        WHEN LEAD(CNT, 1) OVER(Order By "SOURCE", "KEY",  "FIELD") = 2 
                        THEN LEAD("FIELD", 1) OVER(Order By "SOURCE", "KEY",  "FIELD")
                  END as FIELD_2, 
                  "KEY", "LABEL"
              From  ( Select  "SOURCE", "FIELD", "KEY", "LABEL", 
                              Count("FIELD") OVER(Partition By "SOURCE", "KEY" Order By "FIELD") "CNT"
                      From    CONF_TBL
                      Order By "SOURCE", "KEY",  "FIELD" );
--
--  Cursor result
SOURCE               FIELD_1              FIELD_2              KEY                  LABEL               
-------------------- -------------------- -------------------- -------------------- --------------------
ET_1                 FLD_1                                     KEY_1                                    
ET_1                 FLD_2                                     KEY_2                                    
ET_2                 COL_1                COL_2                KEY_2                                    
ET_2                 COL_2                xxx                  KEY_2

Next you should adjust the code to handle two fields...

SET SERVEROUTPUT ON
Declare
    CURSOR c IS 
              Select "SOURCE", "FIELD" as FIELD_1, 
                  CASE  WHEN CNT = 2 THEN 'xxx'
                        WHEN LEAD(CNT, 1) OVER(Order By "SOURCE", "KEY",  "FIELD") = 2 
                        THEN LEAD("FIELD", 1) OVER(Order By "SOURCE", "KEY",  "FIELD")
                  END as FIELD_2, 
                  "KEY", "LABEL"
              From  ( Select  "SOURCE", "FIELD", "KEY", "LABEL", 
                              Count("FIELD") OVER(Partition By "SOURCE", "KEY" Order By "FIELD") "CNT"
                      From    CONF_TBL
                      Order By "SOURCE", "KEY",  "FIELD" );
    mSource   VarChar2(20);
    mField_1   VarChar2(20);
    mField_2   VarChar2(20);
    mKey      VarChar2(20);
    mLabel    VarChar2(20);
    mSQL_sel  VarChar2(1000) := 'Select i.*, ';
    mSQL_from VarChar2(1000) := 'From INPUT_TBL i ';
    mSQL_join VarChar2(1000);
    mSQL      VarChar2(4000);
    cnt       Number  := 0;
    ta        VarChar2(32);
Begin
    OPEN c;
    LOOP
        Fetch c Into mSource, mField_1, mField_2, mKey, mLabel;
        Exit When c%NOTFOUND;
        If mField_2 = 'xxx' Then
            GoTo NextRow;
        End If;
        cnt := cnt + 1;
        ta := 'ta' || cnt;
        --
        mSQL_sel := mSQL_sel || ta || '.' || mField_1 || ' "' || Nvl(mLabel, ta || '_' || mField_1) || '", ';
        If mField_2 Is Not Null And mField_2 != 'xxx' Then
            mSQL_sel := mSQL_sel || ta || '.' || mField_2 || ' "' || Nvl(mLabel, ta || '_' || mField_2) || '_A' || '", ';
        End If;
        If mField_2 Is Null OR mField_2 != 'xxx' Then
            mSQL_join := mSQL_join || 'LEFT JOIN ' || mSource || ' ' || ta || ' ON(' || ta || '.' || mKey || ' = i.' || mKey || ')' || Chr(10);
        End If;
        <<NextRow>>
        Null;
    END LOOP;
    CLOSE c;
    mSQL_sel := SubStr(mSQL_sel, 1, Length(mSQL_sel) -2);
    mSQL_join := SubStr(mSQL_join, 1, Length(mSQL_join) -1);
    mSQL := mSQL_sel || Chr(10) || mSQL_from || Chr(10) || mSQL_join;
    DBMS_OUTPUT.PUT_LINE(mSQL);
End;
/

SQL command and it's result is:

Select i.*, ta1.FLD_1 "ta1_FLD_1", ta2.FLD_2 "ta2_FLD_2", ta3.COL_1 "ta3_COL_1", ta3.COL_2 "ta3_COL_2_A"
From INPUT_TBL i 
LEFT JOIN ET_1 ta1 ON(ta1.KEY_1 = i.KEY_1)
LEFT JOIN ET_1 ta2 ON(ta2.KEY_2 = i.KEY_2)
LEFT JOIN ET_2 ta3 ON(ta3.KEY_2 = i.KEY_2)


PL/SQL procedure successfully completed.

KEY_1                KEY_2                ta1_FLD_1            ta2_FLD_2            ta3_COL_1            ta3_COL_2_A         
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
C                    D                                                              PQR                  STU                 
A                    B                                                                                                       
G                    H                    ABC                  DEF                                                           
E                    F                                                                                                       
K                    L                                                                                                       
I                    J                                                                                                       

6 rows selected.

Thank you! Is there any loss of performance in repeating the same join condition twice as it happens here for ET_2 (and btw, also happens in my real task) or the engine is smart enough to avoid the same join multiple times?
@nicola Just posted an update that handles duplicate joins...
@nicola This doesn't handle two labels - you should do it the same way I did it with two fields..

astentx · Accepted Answer · 2023-03-27 15:24:23Z

For this particular case of fixed column number you may use SQL_MACRO (available since 19.7) and local PL/SQL declaration to generate SQL query with marco and consume it as "standard" SQL result set.

Below is an example.

Prepare some tables:

begin
  for i in 1..5 loop
    execute immediate replace(replace(q'{create table src^^i^^
    as
    select
      lpad(abs(^^reverse^^ - level), 3, '0') as source_key_^^i^^
      , 'This is a row #' || level || ' from src^^i^^' as label_^^i^^
    from dual
    connect by level < 10
    }', '^^i^^', i), '^^reverse^^', mod(i, 2)*99);
  end loop;
end;

Fill config:

insert into conf values('src1', 'source_key_1', 'key2', 'label_1');
insert into conf values('src2', 'source_key_2', 'key1', 'label_2');
insert into conf values('src3', 'source_key_3', 'key1', 'label_3');

Build dynamic query on-the-fly:

with function f_get_query
return varchar2
sql_macro(table)
as
  res varchar2(32000);
begin
  select
    listagg(replace(replace(replace(replace(replace(q'{
        select ^^conf^^ as src, input_.*, c.^^label^^ as label_VALUE
        from input_
          left join ^^tab^^ c
          on input_.^^input_key^^ = c.^^tab_key^^
      }',
      '^^tab^^', dbms_assert.sql_object_name(conf.source)),
      '^^tab_key^^', conf.field),
      '^^input_key^^', conf.key_),
      '^^label^^', conf.label),
      '^^conf^^', dbms_assert.enquote_literal(conf.source)
    ), chr(10) || 'union all' || chr(10)) as res

    into res
  from conf;

  return res;
end;

select *
from f_get_query()
order by 1,2,3

And for this samle data

create table input_ (key1, key2)
as
select
  lpad(level, 3, '0')
  , lpad(99 - level, 3, '0')
from dual
connect by level < 13

it returns

SRC	KEY1	KEY2	LABEL_VALUE
src1	001	098	This is a row #1 from src1
src1	002	097	This is a row #2 from src1
src1	003	096	This is a row #3 from src1
src1	004	095	This is a row #4 from src1
src1	005	094	This is a row #5 from src1
src1	006	093	This is a row #6 from src1
src1	007	092	This is a row #7 from src1
src1	008	091	This is a row #8 from src1
src1	009	090	This is a row #9 from src1
src1	010	089	null
src1	011	088	null
src1	012	087	null
src2	001	098	This is a row #1 from src2
src2	002	097	This is a row #2 from src2
src2	003	096	This is a row #3 from src2
src2	004	095	This is a row #4 from src2
src2	005	094	This is a row #5 from src2
src2	006	093	This is a row #6 from src2
src2	007	092	This is a row #7 from src2
src2	008	091	This is a row #8 from src2
src2	009	090	This is a row #9 from src2
src2	010	089	null
src2	011	088	null
src2	012	087	null
src3	001	098	null
src3	002	097	null
src3	003	096	null
src3	004	095	null
src3	005	094	null
src3	006	093	null
src3	007	092	null
src3	008	091	null
src3	009	090	null
src3	010	089	null
src3	011	088	null
src3	012	087	null

db<>fiddle

Thank you for your reply. From what I see, the output is in the "long" format, while I'd prefer to be wide because of how the python application consumes it. Of course I could reshape it in python, but I might have resources issues.
@nicola So you need to be more specific in this case. It's also possible to have dynamically "wide" result set but with the data known upfront at the execution time, since this particular approach cannot take arbitrary table as input parameter. Anyway, in Oracle you cannot dynamically change result structure because it should be known for the parser before actual execution, so with dynamic SQL you'll have more or less the same issues

Jon Heller · Accepted Answer · 2023-03-26 18:06:35Z

Dynamic SQL in Plain SQL - DBMS_XMLGEN

If you must use plain SQL, and cannot create or call any PL/SQL objects, you can still have dynamic SQL using the DBMS_XMLGEN trick. But there are several downsides to this approach:

The query must return a static number and types and columns. This still might work for you, since you only have one dynamic column. However, that column will always need to be converted to a string, which is usually a terrible choice. And you cannot change the name of the column dynamically, but you can return the label as a separate column.
The query is difficult to build and understand.
Performance may suffer.

Dynamic SQL in PL/SQL Objects - Return Dynamic SYS_REFCURSOR

The more common, and much easier, approach to returning dynamic data is to create a PL/SQL function or procedure that returns a SYS_REFCURSOR built from a string. The minor downsides to this approach are:

You must be able to create a PL/SQL object on the right schema. Keep in mind that your schema does not necessarily need the privileges to create objects; you can ask a privileged user like a DBA to create the simple PL/SQL object for you. If you have a reasonable DBA, it is extremely rare that they won't let you create any objects on a schema.
Your application must be able to consume PL/SQL cursors.

Collectives™ on Stack Overflow

Adding columns to a table depending on a configuration in Oracle SQL

3 Answers 3

3 Comments

2 Comments

Dynamic SQL in Plain SQL - DBMS_XMLGEN

Dynamic SQL in PL/SQL Objects - Return Dynamic SYS_REFCURSOR

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Dynamic SQL in Plain SQL - DBMS_XMLGEN

Dynamic SQL in PL/SQL Objects - Return Dynamic SYS_REFCURSOR

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related