OWB (Oracle Warehouse Builder) Not Generating Right Query For Update. (Doc ID 2355818.1)

Last updated on FEBRUARY 01, 2022

Applies to:

Symptoms

Code is generated in one environment (DEV) that is different from the second environment (TEST)

Query from DEV (Incorrect Code)

FROM
 "ATVI_DDR_B_NET_SHIP_ITEM_DAY" "ATVI_DDR_B_NET_SHIP_ITEM_DAY" ) "BASE" ON ( (( "STAGING"."DAY_CD" = "BASE"."DAY_CD" )) AND (( "STAGING"."ORG_BSNS_UNIT_ID" = "BASE"."ORG_BSNS_UNIT_ID" )) AND (( "STAGING"."RTL_SKU_ITEM_ID" = "BASE"."RTL_SKU_ITEM_ID" )) )) "SPLITTER_INPUT_SUBQUERY"
 WHERE
 ( "SPLITTER_INPUT_SUBQUERY"."DAY_CD_BASE" IS NOT NULL ) /* GROUP SPLITTER.UPDATE_GRP: SPLIT CONDITION*/ AND
 ( "SPLITTER_INPUT_SUBQUERY"."ORG_BSNS_UNIT_ID_BASE" IS NOT NULL ) AND
 ( "SPLITTER_INPUT_SUBQUERY"."RTL_SKU_ITEM_ID_BASE" IS NOT NULL )
 )
  "MERGE_SUBQUERY"
ON (
1=1
)

WHEN MATCHED THEN
 UPDATE
 SET
  "MFG_ORG_CD" = "MERGE_SUBQUERY"."MFG_ORG_CD",






EXPECTED BEHAVIOR
-----------------------
Query from TEST (Correct Code)

FROM
 "ATVI_DDR_B_NET_SHIP_ITEM_DAY" "ATVI_DDR_B_NET_SHIP_ITEM_DAY" ) "BASE" ON ( (( "STAGING"."DAY_CD" = "BASE"."DAY_CD" )) AND (( "STAGING"."ORG_BSNS_UNIT_ID" = "BASE"."ORG_BSNS_UNIT_ID" )) AND (( "STAGING"."RTL_SKU_ITEM_ID" = "BASE"."RTL_SKU_ITEM_ID" )) )) "SPLITTER_INPUT_SUBQUERY"
 WHERE
 ( "SPLITTER_INPUT_SUBQUERY"."DAY_CD_BASE" IS NOT NULL ) /* GROUP SPLITTER.UPDATE_GRP: SPLIT CONDITION*/ AND
 ( "SPLITTER_INPUT_SUBQUERY"."ORG_BSNS_UNIT_ID_BASE" IS NOT NULL ) AND
 ( "SPLITTER_INPUT_SUBQUERY"."RTL_SKU_ITEM_ID_BASE" IS NOT NULL )
 )
  "MERGE_SUBQUERY"
ON (
 "ATVI_DDR_B_NET_SHIP_ITEM_DAY_U"."ORG_BSNS_UNIT_ID" = "MERGE_SUBQUERY"."ORG_BSNS_UNIT_ID" AND
 "ATVI_DDR_B_NET_SHIP_ITEM_DAY_U"."DAY_CD" = "MERGE_SUBQUERY"."DAY_CD" AND
 "ATVI_DDR_B_NET_SHIP_ITEM_DAY_U"."RTL_SKU_ITEM_ID" = "MERGE_SUBQUERY"."RTL_SKU_ITEM_ID"
  )
 
 WHEN MATCHED THEN
  UPDATE
  SET
  "MFG_ORG_CD" = "MERGE_SUBQUERY"."MFG_ORG_CD",

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.