[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
IF delete_cdi_results_table (p_dim_name) = FALSE
THEN
RETURN FALSE;
'SELECT rel.relation_id, rel.relation_name, rel.relation_long_name, pk.column_name, '
|| 'pk.column_id, lvl.level_table_name, tbl.long_name '
|| 'from '
|| 'edw_levels_md_v lvl, '
|| 'edw_relations_md_v rel, '
|| 'edw_relationmapping_md_v map, '
|| 'edw_unique_key_columns_md_v pk, '
|| 'edw_unique_keys_md_v uk, '
|| 'edw_tables_md_v tbl '
|| 'where lvl.dim_name =:s '
|| 'AND map.targetdataentity = lvl.level_table_id '
|| 'AND rel.relation_id = map.sourcedataentity '
|| 'AND lvl.level_table_name = lvl.level_name || ''_LTC'' '
|| 'AND uk.entity_id = rel.relation_id '
|| 'AND pk.key_id = uk.key_id '
|| 'AND lvl.level_table_id = tbl.elementid ';
'SELECT rel.relation_long_name longname '
|| 'FROM edw_levels_md_v lvl, '
|| 'edw_relations_md_v rel '
|| 'WHERE lvl.dim_name = :s '
|| 'AND lvl.level_name = SUBSTR (dim_name, 1, INSTR (dim_name, ''_M'', -1) - 1) || ''_A'' '
|| 'AND rel.relation_name = lvl.level_name || ''_LTC''';
'SELECT hrc.hier_long_name, chil_lvltbl_name, parent_lvltbl_name '
|| 'FROM edw_level_relations_md_v lrl, edw_hierarchies_md_v hrc '
|| 'WHERE lrl.dim_name = :s '
|| 'AND lrl.hier_id = hrc.hier_id '
|| 'ORDER BY chil_lvltbl_name, parent_lvltbl_name';
'SELECT lstg.relation_name, fk.fk_column_name, fk_col.business_name, '
|| 'uk.entity_name, rel.relation_name, pk.column_name, pk_col.business_name '
|| 'FROM edw_relations_md_v lstg, '
|| 'edw_foreign_key_columns_md_v fk, '
|| 'edw_unique_keys_md_v uk, '
|| 'edw_unique_key_columns_md_v pk, '
|| 'edw_all_columns_md_v fk_col, '
|| 'edw_all_columns_md_v pk_col, '
|| 'edw_relations_md_v rel '
|| 'WHERE lstg.relation_id IN ('
|| l_str
|| ') '
|| 'AND fk.entity_id = lstg.relation_id '
|| 'AND uk.key_id = fk.pk_id '
|| 'AND pk.key_id = uk.key_id '
|| 'AND fk.entity_id = fk_col.entity_id '
|| 'AND fk.fk_column_id = fk_col.column_id '
|| 'AND pk.column_id = pk_col.column_id '
|| 'AND rel.relation_id = uk.entity_id '
|| 'AND pk_col.entity_id = uk.entity_id';
g_hier_stmt_num := 'select nvl(count(1),0) from ';
|| ' in (select '
|| g_lstg_pk (i)
|| ' from '
|| g_lstg_tables (i)
|| ' having
count('
|| g_lstg_pk (i)
|| ') =1 group by '
|| g_lstg_pk (i)
|| ' ) ';
|| ' as select '
|| g_lstg_pk (i)
|| ',rowid row_id from '
|| g_lstg_tables (i)
|| ' where collection_status in (''READY'',''DANGLING'',''DUPLICATE'') ';
|| ' as select /*+PARALLEL('
|| g_lstg_tables (i)
|| ','
|| g_parallel
|| ')*/ '
|| g_lstg_pk (i)
|| ','
|| ' rowid row_id from '
|| g_lstg_tables (i)
|| ' where collection_status in '
|| '(''READY'',''DANGLING'',''DUPLICATE'') ';
|| ' as select '
|| g_lstg_pk (i)
|| ' PK ,count(1) dup_count from '
|| g_lstg_pk_table (i)
|| ' having count('
|| g_lstg_pk (i)
|| ')>1 group by '
|| g_lstg_pk (i);
l_stmt := 'select sum(dup_count) from '
|| g_lstg_dup_pk_table (i);
l_stmt := 'select PK,dup_count from '
|| g_lstg_dup_pk_table (i);
|| ' as select /*+ORDERED*/ /*+PARALLEL('
|| g_lstg_fk_table (i)
|| ','
|| g_parallel
|| ')*/ ';
|| ' as select /*+ORDERED*/ ';
|| ' as select /*+ORDERED*/ '
|| g_lstg_fk_hold_table (j)
|| '.row_id ';
|| ' union select '
|| g_lstg_fk_hold_table (j)
|| '.row_id ';
l_stmt := 'select count(1) from '
|| g_lstg_fk_hold_table (j)
|| ' where '
|| g_lstg_fk (j)
|| '=''ALL''';
|| ' as select row_id from '
|| g_lstg_fk_hold_table (j)
|| ' MINUS select row_id from '
|| g_lstg_ok_table (j);
l_stmt := 'select /*+ORDERED*/ '
|| g_lstg_fk_hold_table (j)
|| '.'
|| g_lstg_fk (j);
|| ' as select /*+ORDERED*/ '
|| g_lstg_fk_hold_table (j)
|| '.row_id from '
|| g_lstg_fk_hold_table (j)
|| ','
|| g_parent_ltc_fk_table (j)
|| ' where '
|| g_lstg_fk_hold_table (j)
|| '.'
|| g_lstg_fk (j)
|| '='
|| g_parent_ltc_fk_table (j)
|| '.'
|| g_parent_ltc_fk_table_pk (j);
|| ' as select row_id from '
|| g_lstg_fk_hold_table (j)
|| ' MINUS select row_id from '
|| g_lstg_ok_table (j);
l_stmt := 'select /*+ORDERED*/ '
|| g_lstg_fk_hold_table (j)
|| '.'
|| g_lstg_fk (j);
'select relation_id, relation_long_name from edw_relations_md_v where relation_name =:s';
IF delete_cdi_results_table (p_fact_name) = FALSE
THEN
RETURN FALSE;
'SELECT fstg.relation_id, fstg.relation_name, fstg.relation_long_name, '
|| 'pk.column_name, pk.column_id, pk_col.business_name '
|| 'FROM edw_relations_md_v fstg, edw_relationmapping_md_v map, edw_facts_md_v fact, '
|| 'edw_unique_key_columns_md_v pk, '
|| 'edw_unique_keys_md_v uk, '
|| 'edw_all_columns_md_v pk_col '
|| 'WHERE fact.fact_name = :s '
|| 'AND map.targetdataentity = fact.fact_id '
|| 'AND fstg.relation_id = map.sourcedataentity '
|| 'AND uk.entity_id = fstg.relation_id '
|| 'AND pk.key_id = uk.key_id '
|| 'AND pk.column_id = pk_col.column_id '
|| 'AND uk.entity_id = pk_col.entity_id ';
'SELECT fk_col.fk_column_name, fk_col.fk_column_id, fcol.business_name, '
|| 'dim.dim_id, dim.dim_name, dim.dim_long_name, uk_col.column_name, '
|| 'ucol.business_name '
|| 'FROM edw_relations_md_v fact, '
|| 'edw_foreign_key_columns_md_v fk_col, '
|| 'edw_unique_key_columns_md_v uk_col, '
|| 'edw_dimensions_md_v dim, '
|| 'edw_unique_keys_md_v uk, '
|| 'edw_all_columns_md_v fcol, '
|| 'edw_all_columns_md_v ucol '
|| 'WHERE fact.relation_name = :a '
|| 'and fact.relation_id = fk_col.entity_id '
|| 'and fk_col.pk_id = uk_col.key_id '
|| 'and uk_col.key_id = uk.key_id '
|| 'and uk.entity_id = dim.dim_id '
|| 'and fcol.column_id = fk_col.fk_column_id '
|| 'and fcol.entity_id = fact.relation_id '
|| 'and ucol.column_id = uk_col.column_id '
|| 'and ucol.entity_id = dim.dim_id ';
l_stmt:='select mapping_id '||
'from edw_pvt_map_properties_md_v,edw_relations_md_v '||
'where edw_relations_md_v.relation_name=:1 '||
'and edw_relations_md_v.relation_id=edw_pvt_map_properties_md_v.primary_target ';
g_fact_dup_stmt_num := 'select nvl(sum(count(1)),0) from '
|| g_fk_table
|| ' '
|| ' having count('
|| g_fstg_pk
|| ') > 1 group by '
|| g_fstg_pk;
g_fact_dup_stmt_str := 'select distinct '
|| g_fstg_pk
|| ' from '
|| g_fk_table
|| ' having count('
|| g_fstg_pk
|| ') > 1 group by '
|| g_fstg_pk;
'select nvl(count(1),0) from '
|| g_fk_table
|| ' abc where
not exists (select 1 from '
|| g_fact_dims (i)
|| ' where '
|| g_fact_dims (i)
|| '.'
|| g_fact_dims_pk (i)
|| ' = abc.'
|| g_fstg_fk (i)
|| ') ';
'select distinct abc.'
|| g_fstg_fk (i)
|| ' from '
|| g_fk_table
|| ' abc where not
exists (select 1 from '
|| g_fact_dims (i)
|| ' where '
|| g_fact_dims (i)
|| '.'
|| g_fact_dims_pk (i)
|| ' = abc.'
|| g_fstg_fk (i)
|| ') ';
|| ' as select ';
|| ' as select '
|| g_fstg_pk
|| ' PK,count(1) dup_count from '
|| g_fact_pk_table
|| ' having count('
|| g_fstg_pk
|| ')>1 group by '
|| g_fstg_pk;
l_stmt := 'select sum(dup_count) from '
|| g_fact_dup_pk_table;
l_stmt := 'select PK,dup_count from '
|| g_fact_dup_pk_table;
|| ' as select /*+PARELLEL('
|| g_fstg_name
|| ','
|| g_parallel
|| ')*/ ';
|| ' as select ';
|| ' as select /*+ORDERED*/ /*+PARELLEL('
|| g_fact_dims (i)
|| ','
|| g_parallel
|| ')*/ ';
|| ' as select /*+ORDERED*/ ';
|| ' as select row_id from '
|| g_fact_pk_table
|| ' MINUS select row_id from '
|| g_fact_fk_ok_table (i);
l_stmt := 'select /*+ORDERED*/ '
|| g_fk_table
|| '.'
|| g_fstg_fk (i);
l_stmt := 'select nvl(count(1),0) from '
|| g_fk_table
|| ' A ';
|| ' in (select '
|| g_fstg_pk
|| ' from '
|| g_fk_table
|| ' having count('
|| g_fstg_pk
|| ') = 1 group by '
|| g_fstg_pk
|| ') ';
'LAST_UPDATE_DATE IS NOT NULL'
) = 2
THEN
l_col := 'LAST_UPDATE_DATE';
|| ' as select ';
FUNCTION delete_cdi_results_table (p_object_name IN VARCHAR2)
RETURN BOOLEAN
IS
l_stmt VARCHAR2 (2000);
'select distinct interface_table_id from EDW_CDI_RESULTS where object_name=:a';
l_stmt := 'delete EDW_CDI_KEY_VALUES where table_id=:a';
l_stmt := 'delete '
|| g_results_table
|| ' where object_name=:a';
l_stmt := 'delete edw_cdi_dim_missing_keys where fact_id=:a';
write_to_log_n ( 'Error in delete_cdi_results_table '
|| SQLERRM);
END delete_cdi_results_table;
|| ' as select ';
'select NAME,INSTANCE_CODE,WAREHOUSE_TO_INSTANCE_LINK from edw_source_instances '
|| 'where ENABLED_FLAG=''Y''';
'select distinct parent_table,parent_table_id from edw_cdi_results where number_dangling>0 '
|| 'and object_name=:a';
l_stmt := 'select distinct parent_table_id,instance from '
|| g_dim_missing_keys_op
|| ' order by instance';
|| ' as select A.rowid row_id,B.dim_pk_date pk_date,A.key_value,A.number_key_value '
|| 'from '
|| g_dim_missing_keys_op
|| ' A,'
|| p_view
|| '@'
|| p_instance_link
|| ' B where '
|| 'A.parent_table_id='
|| p_dim_id
|| ' and A.instance='''
|| p_instance
|| ''' and B.dim_pk=A.key_value';
l_stmt := 'select min(pk_date),max(pk_date) from '
|| l_table;
'insert into edw_cdi_dim_missing_keys(dim_id,fact_id,instance,key_value,number_key_value,'
|| 'missing_date) select '
|| p_dim_id
|| ','
|| p_fact_id
|| ','''
|| p_instance
|| ''',a.key_value,a.number_key_value,'
|| 'b.day_pk_key from '
|| l_table
|| ' a,edw_time_day_ltc b where to_char(a.pk_date,''DD-MM-YYYY'')=b.day_pk';
write_to_log_n ( 'Inserted '
|| SQL%ROWCOUNT
|| ' rows '
|| get_time);
|| 'select row_id from '
|| l_tables (i)
|| ' UNION ALL ';
|| ' as select rowid row_id from '
|| g_dim_missing_keys_op
|| ' where '
|| 'parent_table_id='
|| p_dang_dim_id
|| ' MINUS select row_id from '
|| l_op_table;
|| ' as select /*+ORDERED*/ A.key_value,A.instance from '
|| l_rowid_table
|| ' B, '
|| g_dim_missing_keys_op
|| ' A where B.row_id=A.rowid';