I am currently trying to recode a part of package which extracts data from a source system and processes in the datawarehouse. Basically what the code does is:
**If it is a daily processing processing (p_run_type <> 1) and p_processing_lo cation is satisfied, distinct product ids will be inserted into v_table_name (which is basically a variable) from 3 different tables (using Union - to eliminate duplicate records)
**If it is not a daily processing (p_run_type = 1) and p_processing_lo cation is satisfied, the same process will run as above, only, product ids will be inserted into v_table_name from 4 different tables (the same union of 3 tables above will be used plus union of 1 new table (which is only included if it is not a daily processing)
The code works fine, however, it is not well done coz given the current structure, if an update needs to be done, it should in two places - one on the first If condition (for the non daily processing) and another on the 2nd If condition (for the daily processing) which makes it very prone to errors.
The requirement is to rewrite the code in such a way that it is easy to efficiently manage updates and to reduce redundant queries.
I have two options which I am not sure if feasible at all (and considered to be best practice given the scenario). I am not a hard-core programmer and I need some help in determining the best possible option to make the code better:
OPTION A
1) Put the main query (3 tables) into a variable (named main_query) and the small query (1 table) into a variable (named sub_query)
2) If it is adjusted processing, execute main_query union sub_query
3) Else execute main_query
OPTION B
1) Run first the main query regardless of any processing day. This will insert data into v_table_name
2) If it is adjusted, run a query which is something like this
Insert into v_table_name
Select distinct product_ids
from opd_trans_produ ct
where product_ids not in (select product_ids from v_table_name)
Below is the part of the code that needs to be modified:
IF p_run_type = 1 AND (p_processing_l ocation = 'AMS' OR p_processing_lo cation = 'ALL') THEN
EXECUTE IMMEDIATE
'INSERT INTO ' || v_table_name || '
SELECT DISTINCT To_Number(produ ct_id) product_id
FROM opo_enriched_tb _extract tb
WHERE
(
((''' || p_processing_lo cation || '''= ''PAC'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''PAC'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_PAC, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_PAC) || '))
OR ((''' || p_processing_lo cation || '''= ''EUR'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''EUR'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_EUR, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_EUR) || '))
OR ((''' || p_processing_lo cation || '''= ''AMS'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''AMS'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_AMS, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_AMS) || '))
)
AND product_id_type _cd = ''PIDC''
AND product_id IS NOT NULL
AND RTrim(product_i d,'' 1234567890'') IS NULL
-- UNDERLYING
UNION
SELECT DISTINCT To_Number(produ ct_underlying_i d) product_id
FROM opo_enriched_tb _extract tb
WHERE
(
((''' || p_processing_lo cation || '''= ''PAC'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''PAC'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_PAC, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_PAC) || '))
OR ((''' || p_processing_lo cation || '''= ''EUR'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''EUR'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_EUR, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_EUR) || '))
OR ((''' || p_processing_lo cation || '''= ''AMS'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''AMS'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_AMS, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_AMS) || '))
)
AND product_underly ing_id_type_cd = ''PIDC''
AND product_underly ing_id IS NOT NULL
AND RTrim(product_u nderlying_id,'' 1234567890'') IS NULL
-- ISSUER TB PRODUCTS
UNION
SELECT DISTINCT To_Number(ISSUE R_NOTL_PRODUCT_ ID) product_id
FROM OPO_ENRICHED_IS SUER_TB tb
WHERE
(
((''' || p_processing_lo cation || '''= ''PAC'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''PAC'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_PAC, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_PAC) || '))
OR ((''' || p_processing_lo cation || '''= ''EUR'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''EUR'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_EUR, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_EUR) || '))
OR ((''' || p_processing_lo cation || '''= ''AMS'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''AMS'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_AMS, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_AMS) || '))
)
AND ISSUER_NOTL_PRO DUCT_ID_TYPE_CD = ''PIDC''
AND ISSUER_NOTL_PRO DUCT_ID IS NOT NULL
AND RTrim(ISSUER_NO TL_PRODUCT_ID,' ' 1234567890'') IS NULL
-- POSITION FILES RECORDS
UNION
SELECT DISTINCT To_Number(p_id_ c) product_id
FROM insight_securit y tb
WHERE ref_date = To_Date(''' || To_Char(lv_cob_ date, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'')
AND p_id_c IS NOT NULL
AND RTrim(p_id_c,'' 1234567890'') IS NULL
-- TICS TRANS PROD RECORDS
UNION
SELECT DISTINCT To_Number(produ ct_id) product_id
FROM opd_trans_produ ct tp
WHERE
(
((''' || p_processing_lo cation || '''= ''AMS'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''AMS'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_AMS, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_AMS) || '))
)
AND allocation_id_t ype_cd = ''TICS''
AND product_id IS NOT NULL
AND RTrim(product_i d,'' 1234567890'') IS NULL';
ELSE
EXECUTE IMMEDIATE
'INSERT INTO ' || v_table_name || '
SELECT DISTINCT To_Number(produ ct_id) product_id
FROM opo_enriched_tb _extract tb
WHERE
(
((''' || p_processing_lo cation || '''= ''PAC'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''PAC'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_PAC, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_PAC) || '))
OR ((''' || p_processing_lo cation || '''= ''EUR'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''EUR'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_EUR, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_EUR) || '))
OR ((''' || p_processing_lo cation || '''= ''AMS'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''AMS'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_AMS, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_AMS) || '))
)
AND product_id_type _cd = ''PIDC''
AND product_id IS NOT NULL
AND RTrim(product_i d,'' 1234567890'') IS NULL
-- UNDERLYING
UNION
SELECT DISTINCT To_Number(produ ct_underlying_i d) product_id
FROM opo_enriched_tb _extract tb
WHERE
(
((''' || p_processing_lo cation || '''= ''PAC'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''PAC'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_PAC, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_PAC) || '))
OR ((''' || p_processing_lo cation || '''= ''EUR'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''EUR'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_EUR, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_EUR) || '))
OR ((''' || p_processing_lo cation || '''= ''AMS'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''AMS'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_AMS, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_AMS) || '))
)
AND product_underly ing_id_type_cd = ''PIDC''
AND product_underly ing_id IS NOT NULL
AND RTrim(product_u nderlying_id,'' 1234567890'') IS NULL
-- ISSUER TB PRODUCTS
UNION
SELECT DISTINCT To_Number(ISSUE R_NOTL_PRODUCT_ ID) product_id
FROM OPO_ENRICHED_IS SUER_TB tb
WHERE
(
((''' || p_processing_lo cation || '''= ''PAC'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''PAC'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_PAC, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_PAC) || '))
OR ((''' || p_processing_lo cation || '''= ''EUR'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''EUR'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_EUR, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_EUR) || '))
OR ((''' || p_processing_lo cation || '''= ''AMS'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''AMS'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_AMS, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_AMS) || '))
)
AND ISSUER_NOTL_PRO DUCT_ID_TYPE_CD = ''PIDC''
AND ISSUER_NOTL_PRO DUCT_ID IS NOT NULL
AND RTrim(ISSUER_NO TL_PRODUCT_ID,' ' 1234567890'') IS NULL
-- POSITION FILES RECORDS
UNION
SELECT DISTINCT To_Number(p_id_ c) product_id
FROM insight_securit y tb
WHERE ref_date = To_Date(''' || To_Char(lv_cob_ date, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'')
AND p_id_c IS NOT NULL
AND RTrim(p_id_c,'' 1234567890'') IS NULL';
END IF;
COMMIT;
I am hoping that you could help me out on this.
**If it is a daily processing processing (p_run_type <> 1) and p_processing_lo cation is satisfied, distinct product ids will be inserted into v_table_name (which is basically a variable) from 3 different tables (using Union - to eliminate duplicate records)
**If it is not a daily processing (p_run_type = 1) and p_processing_lo cation is satisfied, the same process will run as above, only, product ids will be inserted into v_table_name from 4 different tables (the same union of 3 tables above will be used plus union of 1 new table (which is only included if it is not a daily processing)
The code works fine, however, it is not well done coz given the current structure, if an update needs to be done, it should in two places - one on the first If condition (for the non daily processing) and another on the 2nd If condition (for the daily processing) which makes it very prone to errors.
The requirement is to rewrite the code in such a way that it is easy to efficiently manage updates and to reduce redundant queries.
I have two options which I am not sure if feasible at all (and considered to be best practice given the scenario). I am not a hard-core programmer and I need some help in determining the best possible option to make the code better:
OPTION A
1) Put the main query (3 tables) into a variable (named main_query) and the small query (1 table) into a variable (named sub_query)
2) If it is adjusted processing, execute main_query union sub_query
3) Else execute main_query
OPTION B
1) Run first the main query regardless of any processing day. This will insert data into v_table_name
2) If it is adjusted, run a query which is something like this
Insert into v_table_name
Select distinct product_ids
from opd_trans_produ ct
where product_ids not in (select product_ids from v_table_name)
Below is the part of the code that needs to be modified:
IF p_run_type = 1 AND (p_processing_l ocation = 'AMS' OR p_processing_lo cation = 'ALL') THEN
EXECUTE IMMEDIATE
'INSERT INTO ' || v_table_name || '
SELECT DISTINCT To_Number(produ ct_id) product_id
FROM opo_enriched_tb _extract tb
WHERE
(
((''' || p_processing_lo cation || '''= ''PAC'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''PAC'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_PAC, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_PAC) || '))
OR ((''' || p_processing_lo cation || '''= ''EUR'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''EUR'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_EUR, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_EUR) || '))
OR ((''' || p_processing_lo cation || '''= ''AMS'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''AMS'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_AMS, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_AMS) || '))
)
AND product_id_type _cd = ''PIDC''
AND product_id IS NOT NULL
AND RTrim(product_i d,'' 1234567890'') IS NULL
-- UNDERLYING
UNION
SELECT DISTINCT To_Number(produ ct_underlying_i d) product_id
FROM opo_enriched_tb _extract tb
WHERE
(
((''' || p_processing_lo cation || '''= ''PAC'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''PAC'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_PAC, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_PAC) || '))
OR ((''' || p_processing_lo cation || '''= ''EUR'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''EUR'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_EUR, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_EUR) || '))
OR ((''' || p_processing_lo cation || '''= ''AMS'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''AMS'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_AMS, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_AMS) || '))
)
AND product_underly ing_id_type_cd = ''PIDC''
AND product_underly ing_id IS NOT NULL
AND RTrim(product_u nderlying_id,'' 1234567890'') IS NULL
-- ISSUER TB PRODUCTS
UNION
SELECT DISTINCT To_Number(ISSUE R_NOTL_PRODUCT_ ID) product_id
FROM OPO_ENRICHED_IS SUER_TB tb
WHERE
(
((''' || p_processing_lo cation || '''= ''PAC'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''PAC'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_PAC, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_PAC) || '))
OR ((''' || p_processing_lo cation || '''= ''EUR'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''EUR'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_EUR, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_EUR) || '))
OR ((''' || p_processing_lo cation || '''= ''AMS'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''AMS'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_AMS, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_AMS) || '))
)
AND ISSUER_NOTL_PRO DUCT_ID_TYPE_CD = ''PIDC''
AND ISSUER_NOTL_PRO DUCT_ID IS NOT NULL
AND RTrim(ISSUER_NO TL_PRODUCT_ID,' ' 1234567890'') IS NULL
-- POSITION FILES RECORDS
UNION
SELECT DISTINCT To_Number(p_id_ c) product_id
FROM insight_securit y tb
WHERE ref_date = To_Date(''' || To_Char(lv_cob_ date, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'')
AND p_id_c IS NOT NULL
AND RTrim(p_id_c,'' 1234567890'') IS NULL
-- TICS TRANS PROD RECORDS
UNION
SELECT DISTINCT To_Number(produ ct_id) product_id
FROM opd_trans_produ ct tp
WHERE
(
((''' || p_processing_lo cation || '''= ''AMS'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''AMS'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_AMS, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_AMS) || '))
)
AND allocation_id_t ype_cd = ''TICS''
AND product_id IS NOT NULL
AND RTrim(product_i d,'' 1234567890'') IS NULL';
ELSE
EXECUTE IMMEDIATE
'INSERT INTO ' || v_table_name || '
SELECT DISTINCT To_Number(produ ct_id) product_id
FROM opo_enriched_tb _extract tb
WHERE
(
((''' || p_processing_lo cation || '''= ''PAC'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''PAC'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_PAC, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_PAC) || '))
OR ((''' || p_processing_lo cation || '''= ''EUR'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''EUR'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_EUR, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_EUR) || '))
OR ((''' || p_processing_lo cation || '''= ''AMS'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''AMS'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_AMS, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_AMS) || '))
)
AND product_id_type _cd = ''PIDC''
AND product_id IS NOT NULL
AND RTrim(product_i d,'' 1234567890'') IS NULL
-- UNDERLYING
UNION
SELECT DISTINCT To_Number(produ ct_underlying_i d) product_id
FROM opo_enriched_tb _extract tb
WHERE
(
((''' || p_processing_lo cation || '''= ''PAC'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''PAC'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_PAC, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_PAC) || '))
OR ((''' || p_processing_lo cation || '''= ''EUR'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''EUR'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_EUR, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_EUR) || '))
OR ((''' || p_processing_lo cation || '''= ''AMS'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''AMS'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_AMS, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_AMS) || '))
)
AND product_underly ing_id_type_cd = ''PIDC''
AND product_underly ing_id IS NOT NULL
AND RTrim(product_u nderlying_id,'' 1234567890'') IS NULL
-- ISSUER TB PRODUCTS
UNION
SELECT DISTINCT To_Number(ISSUE R_NOTL_PRODUCT_ ID) product_id
FROM OPO_ENRICHED_IS SUER_TB tb
WHERE
(
((''' || p_processing_lo cation || '''= ''PAC'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''PAC'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_PAC, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_PAC) || '))
OR ((''' || p_processing_lo cation || '''= ''EUR'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''EUR'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_EUR, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_EUR) || '))
OR ((''' || p_processing_lo cation || '''= ''AMS'' OR ''' || p_processing_lo cation || '''= ''ALL'') AND (processing_loc ation = ''AMS'' AND business_date = To_Date(''' || To_Char(lv_cob_ date_AMS, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_ id_AMS) || '))
)
AND ISSUER_NOTL_PRO DUCT_ID_TYPE_CD = ''PIDC''
AND ISSUER_NOTL_PRO DUCT_ID IS NOT NULL
AND RTrim(ISSUER_NO TL_PRODUCT_ID,' ' 1234567890'') IS NULL
-- POSITION FILES RECORDS
UNION
SELECT DISTINCT To_Number(p_id_ c) product_id
FROM insight_securit y tb
WHERE ref_date = To_Date(''' || To_Char(lv_cob_ date, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'')
AND p_id_c IS NOT NULL
AND RTrim(p_id_c,'' 1234567890'') IS NULL';
END IF;
COMMIT;
I am hoping that you could help me out on this.