I have the following insert query:
INSERT INTO dmf_val_error_log
WITH min_loc_trait AS
(
SELECT h.loc_trait,
h.description loc_trait_desc, -- To compare with this brand division (last 3 CHARS of div_name)
d.STORE,
--- MIN(d.loc_trait) OVER (PARTITION BY d.STORE) store_min_loc_trait,
COUNT(*) OVER (PARTITION BY d.STORE) store_cnt
FROM mig_loc_traits h,
mig_loc_traits_matrix d
WHERE h.loc_trait = d.loc_trait
),
this_brand_loc_trait AS
(
SELECT mlt.loc_trait,
regexp_substr(mlt.loc_trait_desc, '[^_]+', 1, 2) brand,
mlt.store
FROM min_loc_trait mlt,
mig_division d
WHERE d.div_name LIKE regexp_substr(mlt.loc_trait_desc, '[^_]+', 1, 2) || '_' || '%'
GROUP BY mlt.loc_trait,
regexp_substr(mlt.loc_trait_desc, '[^_]+', 1, 2),
mlt.store
),
valid_brand_loc_trait AS
(
SELECT d.loc_trait,
regexp_substr(d.loc_trait_desc, '[^_]+', 1, 2) brand,
d.store
FROM min_loc_trait d
WHERE regexp_substr(d.loc_trait_desc, '[^_]+', 1, 2) = 'DEB'
AND d.store_cnt - 1 = 1
AND EXISTS (SELECT 1
FROM this_brand_loc_trait bvs
WHERE bvs.STORE = d.store)
GROUP BY d.loc_trait,
regexp_substr(d.loc_trait_desc, '[^_]+', 1, 2),
d.store
UNION ALL
SELECT d.loc_trait,
d.brand,
d.store
FROM this_brand_loc_trait d
)
SELECT
'BRAND/ATTRIBUTE_1/ATTRIBUTE_2=S combination does not match any existing MIG_LOC_TRAITS/MIG_LOC_TRAITS_MATRIX setup', -- error_desc
FROM mig_als_int_cross_ref_dmf_ccid crs,
v_brand_store s
WHERE crs.attribute_2 = 'S'
AND crs.attribute_1 = s.store
AND s.is_store_min_brand = 'Y'
AND NOT EXISTS (SELECT 1
FROM valid_brand_loc_trait lt
WHERE lt.store = TO_NUMBER(crs.attribute_1)
AND lt.brand = crs.brand);
When I run the underlying SQL statement, it doesn't bring any record. But If I run with the inserts, it inserts records on it.
The following statement is executed initially on the server:
ALTER SESSION FORCE PARALLEL DML PARALLEL 50;
If this statement is executed and then the insert is run, it inserted records. But if this parallel statement is not executed, the insert query doesn't insert anything.
What can be the issue with the above parallel force and the query?
UPDATE: - Use Case
When running the Select statement under the Insert, it brings 0-Records
Just by un-commenting the insert, it inserts records from the same SQL
