Skip to Main Content
  • Questions
  • Adding Dinamic Nulls to perform Union

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Aravinda.

Asked: June 05, 2017 - 4:25 pm UTC

Last updated: June 06, 2017 - 2:56 am UTC

Version: 11.2.0.1

Viewed 1000+ times

You Asked

Hello Sir,
it's the first time I write to you!

I've the necessity of listing per table the age of data (oldest data thet we have in the table).


i'm trying to generate a dinamic sqls for each table of the schema like:
SELECT'ALL_OBJECTS', MIN(CREATED), MIN(LAST_DDL_TIME) from ALL_OBJECTS;
SELECT 'USER_ADDM_TASKS',MIN(CREATED), MIN(LAST_MODIFIED), MIN(EXECUTION_START), MIN(EXECUTION_END)from USER_ADDM_TASKS;

...

my attemp:
SELECT *
  FROM (
SELECT l_tname,
       'select ' || '''' || l_tname || ''',' || SUBSTR (cl, 1, LENGTH (cl) - 1) || 'from ' || l_tname,
       ROW_NUMBER () OVER (PARTITION BY l_tname ORDER BY ROWNUM) num
  FROM (SELECT table_name l_tname,
               (SELECT REPLACE (XMLAGG (XMLELEMENT (e, 
                                                       CASE WHEN data_type = 'DATE' 
                                                            THEN Q'#MIN(#' || COLUMN_NAME || ')' --else ''
                                                       END, ',') 
                                        ORDER BY column_id ASC).EXTRACT ('//text()').getClobVal (), '&' || 'apos;', '''')
                          cl2
                  FROM all_tab_cols
                 WHERE table_name = tab1.table_name AND data_type = 'DATE')
                  cl
          FROM all_tab_cols tab1
         WHERE table_name IN ('ALL_OBJECTS', 'USER_ADDM_TASKS') AND data_type = 'DATE')
)
 WHERE num = 1  



now I need to add dinamicaly the null columns befor concatenating the ||'union all'

it would have to look like:
select 'ALL_OBJECTS',MIN(CREATED),MIN(LAST_DDL_TIME), NULL, NULL from ALL_OBJECTS UNION ALL
select 'USER_ADDM_TASKS',MIN(CREATED),MIN(LAST_MODIFIED),MIN(EXECUTION_START),MIN(EXECUTION_END)from USER_ADDM_TASKS;


I know i'll have at most 10 Date columns per table in my schema:
SELECT TABLE_NAME, COUNT(*)FROM ALL_TAB_COLS
WHERE DATA_TYPE='DATE'
GROUP BY TABLE_NAME
ORDER BY 2 DESC

so I'd need to fill dinamicly the null columns to get the total of 10 columns in each select in order to have a union all.

Could you help?

Thank you very much.

and Connor said...

To do this, I create "fake" versions of all_tab_cols which has up to 10 artificial columns added all as null, ie

SQL> with modified_all_tables as
  2  ( select distinct owner, table_name
  3    from all_tab_cols
  4    where data_type = 'DATE' ),
  5  modified_all_tab_cols as
  6  ( select owner,
  7           table_name,
  8           'min('||column_name||')' column_name,
  9           column_id
 10    from   all_tab_cols
 11    where  data_type = 'DATE'
 12    union all
 13    select owner,
 14           table_name,
 15           'null' column_name,
 16           2000+d
 17    from   modified_all_tables,
 18           ( select rownum d from dual connect by level <= 10 )
 19  )
 20  SELECT table_name,
 21         (SELECT XMLAGG(XMLELEMENT(e,column_name,',') ORDER BY column_id ASC).EXTRACT ('//text()').getClobVal () cl2
 22          FROM modified_all_tab_cols
 23          WHERE table_name = t1.table_name) cl
 24  FROM   modified_all_tables t1
 25  WHERE table_name IN ('ALL_OBJECTS', 'USER_ADDM_TASKS');

TABLE_NAME
------------------------------
CL
------------------------------------------------------------------------------------------------------------------------------
ALL_OBJECTS
min(CREATED),min(LAST_DDL_TIME),null,null,null,null,null,null,null,null,null,null,

USER_ADDM_TASKS
min(CREATED),min(LAST_MODIFIED),min(EXECUTION_START),min(EXECUTION_END),null,null,null,null,null,null,null,null,null,null,


so now that I have that, all I need do is pick out the text up to the 10th comma, ie,

SQL> with modified_all_tables as
  2  ( select distinct owner, table_name
  3    from all_tab_cols
  4    where data_type = 'DATE' ),
  5  modified_all_tab_cols as
  6  ( select owner,
  7           table_name,
  8           'min('||column_name||')' column_name,
  9           column_id
 10    from   all_tab_cols
 11    where  data_type = 'DATE'
 12    union all
 13    select owner,
 14           table_name,
 15           'null' column_name,
 16           2000+d
 17    from   modified_all_tables,
 18           ( select rownum d from dual connect by level <= 10 )
 19  )
 20  select table_name,
 21         substr(cl,1,instr(cl,',',1,10)-1) str
 22  from
 23  (
 24  SELECT table_name,
 25         (SELECT XMLAGG(XMLELEMENT(e,column_name,',') ORDER BY column_id ASC).EXTRACT ('//text()').getClobVal () cl2
 26          FROM modified_all_tab_cols
 27          WHERE table_name = t1.table_name) cl
 28          FROM   modified_all_tables t1
 29  WHERE table_name IN ('ALL_OBJECTS', 'USER_ADDM_TASKS')
 30  );

TABLE_NAME
------------------------------
STR
-------------------------------------------------------------------------------------------------------------------------
ALL_OBJECTS
min(CREATED),min(LAST_DDL_TIME),null,null,null,null,null,null,null,null

USER_ADDM_TASKS
min(CREATED),min(LAST_MODIFIED),min(EXECUTION_START),min(EXECUTION_END),null,null,null,null,null,null


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Partitioned outer joins

Rajeshwaran, Jeyabal, June 07, 2017 - 11:52 am UTC

Other option would be to use Partitioned Outer joins.

demo@ORA11G> with t1 as (
  2    select table_name,column_name,
  3            row_number() over( partition by table_name order by column_name)  rn
  4    from all_tab_cols
  5    where table_name in ('ALL_OBJECTS', 'USER_ADDM_TASKS')
  6    and data_type = 'DATE'   ) ,
  7  datas as ( select level l
  8    from dual
  9    connect by level <= 10 )
 10  select t1.table_name, column_name , d.l ,
 11              case when column_name is not null then
 12                      'min('||column_name||')' else 'null' end new_column_name
 13  from t1 partition by (table_name) right outer join datas d
 14  on ( t1.rn = d.l) ;

TABLE_NAME           COLUMN_NAME                   L NEW_COLUMN_NAME
-------------------- -------------------- ---------- -----------------------------------
ALL_OBJECTS          CREATED                       1 min(CREATED)
ALL_OBJECTS          LAST_DDL_TIME                 2 min(LAST_DDL_TIME)
ALL_OBJECTS                                        3 null
ALL_OBJECTS                                        4 null
ALL_OBJECTS                                        5 null
ALL_OBJECTS                                        6 null
ALL_OBJECTS                                        7 null
ALL_OBJECTS                                        8 null
ALL_OBJECTS                                        9 null
ALL_OBJECTS                                       10 null
USER_ADDM_TASKS      CREATED                       1 min(CREATED)
USER_ADDM_TASKS      EXECUTION_END                 2 min(EXECUTION_END)
USER_ADDM_TASKS      EXECUTION_START               3 min(EXECUTION_START)
USER_ADDM_TASKS      LAST_MODIFIED                 4 min(LAST_MODIFIED)
USER_ADDM_TASKS                                    5 null
USER_ADDM_TASKS                                    6 null
USER_ADDM_TASKS                                    7 null
USER_ADDM_TASKS                                    8 null
USER_ADDM_TASKS                                    9 null
USER_ADDM_TASKS                                   10 null

20 rows selected.


then use listagg on top of this to get a final query string like this.

demo@ORA11G> column str format a150
demo@ORA11G> select  'select '||listagg(new_column_name,',') within group(order by l) ||' from '||table_name str
  2  from (
  3  with t1 as (
  4    select table_name,column_name,
  5            row_number() over( partition by table_name order by column_name)  rn
  6    from all_tab_cols
  7    where table_name in ('ALL_OBJECTS', 'USER_ADDM_TASKS')
  8    and data_type = 'DATE'   ) ,
  9  datas as ( select level l
 10    from dual
 11    connect by level <= 10 )
 12  select t1.table_name, column_name , d.l ,
 13              case when column_name is not null then
 14                      'min('||column_name||')' else 'null' end new_column_name
 15  from t1 partition by (table_name) right outer join datas d
 16  on ( t1.rn = d.l)
 17      )
 18  group by table_name    ;

STR
-----------------------------------------------------------------------------------------------------------------------------------
select min(CREATED),min(LAST_DDL_TIME),null,null,null,null,null,null,null,null from ALL_OBJECTS
select min(CREATED),min(EXECUTION_END),min(EXECUTION_START),min(LAST_MODIFIED),null,null,null,null,null,null from USER_ADDM_TASKS

demo@ORA11G>

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.