Store 83 CATA VS PUB

store83DuplicateItems.sql

SELECT 
  *
  FROM
    (
      SELECT 
        PRICE_ID AS CATA_PRICE_ID,
        VERSION AS CATA_VERSION,
        REGEXP_SUBSTR(PRICE_LIST, '[^;]+') AS CATA_STORE,
        REGEXP_SUBSTR(PRICE_LIST, '[a-zA-Z]+') AS CATA_PRICE_TYPE,
        SKU_ID AS CATA_SKU_ID,
        LIST_PRICE AS CATA_LIST_PRICE
        
        FROM 
          WLM_PROD_CATA.DCS_PRICE
    ) CATA_PRICE
    
  LEFT JOIN
    (
      SELECT
        WORKSPACE_ID
        BRANCH_ID,
        IS_HEAD, 
        VERSION_DELETED,
        VERSION_EDITABLE,
        PRED_VERSION,
        CHECKIN_DATE,
        PRICE_ID AS PUB_PRICE_ID,
        VERSION AS PUB_VERSION,
        REGEXP_SUBSTR(PRICE_LIST, '[^;]+') AS PUB_STORE,
        REGEXP_SUBSTR(PRICE_LIST, '[a-zA-Z]+') AS PUB_PRICE_TYPE,
        SKU_ID AS PUB_SKU_ID
          FROM WLM_PROD_PUB.DCS_PRICE  
    ) PUB_PRICE
    
    ON 
      CATA_PRICE.CATA_STORE = PUB_PRICE.PUB_STORE
        AND CATA_PRICE.CATA_PRICE_TYPE = PUB_PRICE.PUB_PRICE_TYPE
        AND CATA_PRICE.CATA_SKU_ID = PUB_PRICE.PUB_SKU_ID
        
    WHERE CATA_PRICE.CATA_STORE = '83';