BDNA Data Platform 5.4 July 2017 (SQL Server) CAT_SW_TAXONOMY2012 - Bridge table between CAT_SW_PRODUCT and TAXONOMY2012
|
View Definition:
CREATE VIEW CAT_SW_TAXONOMY2012 AS WITH tax_full AS ( SELECT CAT_SW_TAXONOMY2012_ID, CREATE_DATE, LAST_MODIFIED_DATE, TAXONOMY_VERSION, CASE WHEN CAT_TYPE='PRI' THEN TAXONOMY_DEFAULT
WHEN CAT_TYPE='PUB' THEN
CASE WHEN ISNULL(DEF_SUM,0) = 0 THEN TAXONOMY_DEFAULT
WHEN DEF_SUM > 0 THEN NULL
END
END TAXONOMY_DEFAULT , CAT_TAG_ID, CAT_SW_PRODUCT_ID, CAT_TAXONOMY2012_ID, TO_BE_DELETED, TO_BE_DELETED_ON, DELETE_REASON, PRIVATE_FLAG, IS_PRIVATE, PRIVATE_TYPE, REPLACEMENT_ID,CAT_TYPE FROM ( SELECT f_tcat.CAT_SW_TAXONOMY2012_ID, f_tcat.CREATE_DATE, f_tcat.LAST_MODIFIED_DATE, f_tcat.TAXONOMY_VERSION, f_tcat.TAXONOMY_DEFAULT, f_tcat.CAT_TAG_ID, f_tcat.CAT_SW_PRODUCT_ID, f_tcat.CAT_TAXONOMY2012_ID, f_tcat.TO_BE_DELETED, f_tcat.TO_BE_DELETED_ON, f_tcat.DELETE_REASON, f_tcat.PRIVATE_FLAG, f_tcat.IS_PRIVATE, f_tcat.PRIVATE_TYPE, f_tcat.REPLACEMENT_ID,'PUB' CAT_TYPE
,SUM(tpc.TAXONOMY_DEFAULT) OVER (partition by tpc.CAT_SW_PRODUCT_ID,tpc.CAT_TAG_ID) DEF_SUM
,CASE WHEN tpc.CAT_SW_PRODUCT_ID IS NOT NULL THEN
ROW_NUMBER() OVER (PARTITION BY tpc.CAT_SW_PRODUCT_ID,tpc.CAT_TAG_ID ORDER BY tpc.CAT_SW_PRODUCT_ID desc)
ELSE 1 END RN FROM TCAT_SW_TAXONOMY2012 f_tcat
LEFT JOIN TPC_SW_TAXONOMY2012 tpc ON f_tcat.CAT_SW_PRODUCT_ID=tpc.CAT_SW_PRODUCT_ID AND f_tcat.CAT_TAG_ID= tpc.CAT_TAG_ID UNION ALL SELECT tpc.CAT_SW_TAXONOMY2012_ID, tpc.CREATE_DATE, tpc.LAST_MODIFIED_DATE, tpc.TAXONOMY_VERSION, tpc.TAXONOMY_DEFAULT, tpc.CAT_TAG_ID, tpc.CAT_SW_PRODUCT_ID, tpc.CAT_TAXONOMY2012_ID, tpc.TO_BE_DELETED, tpc.TO_BE_DELETED_ON, tpc.DELETE_REASON, tpc.PRIVATE_FLAG, tpc.IS_PRIVATE, tpc.PRIVATE_TYPE, tpc.REPLACEMENT_ID,'PRI' CAT_TYPE
,NULL DEF_SUM
,NULL RN FROM TPC_SW_TAXONOMY2012 tpc ) tax_v WHERE 1=1 AND ISNULL(RN,1) = 1 ) SELECT CAT_SW_TAXONOMY2012_ID, CREATE_DATE, LAST_MODIFIED_DATE, TAXONOMY_VERSION, TAXONOMY_DEFAULT, CAT_TAG_ID, CAT_SW_PRODUCT_ID, CAT_TAXONOMY2012_ID, TO_BE_DELETED, TO_BE_DELETED_ON, DELETE_REASON, PRIVATE_FLAG, IS_PRIVATE, PRIVATE_TYPE, REPLACEMENT_ID FROM TAX_FULL f_tcat
WHERE CAT_TYPE='PUB'
AND NOT EXISTS
(SELECT 1
FROM TPC_SW_TAXONOMY2012 tpc_tab
WHERE tpc_tab.CAT_SW_TAXONOMY2012_ID=f_tcat.CAT_SW_TAXONOMY2012_ID
) UNION ALL SELECT CAT_SW_TAXONOMY2012_ID, CREATE_DATE, LAST_MODIFIED_DATE, TAXONOMY_VERSION, TAXONOMY_DEFAULT, CAT_TAG_ID, CAT_SW_PRODUCT_ID, CAT_TAXONOMY2012_ID, TO_BE_DELETED, TO_BE_DELETED_ON, DELETE_REASON, PRIVATE_FLAG, IS_PRIVATE, PRIVATE_TYPE, REPLACEMENT_ID FROM (SELECT COALESCE(tpc.CAT_SW_TAXONOMY2012_ID,tcat.CAT_SW_TAXONOMY2012_ID) CAT_SW_TAXONOMY2012_ID,COALESCE(tpc.CREATE_DATE,tcat.CREATE_DATE) CREATE_DATE,CASE WHEN ISNULL(tpc.LAST_MODIFIED_DATE,'31-DEC-1900') > tcat.LAST_MODIFIED_DATE THEN tpc.LAST_MODIFIED_DATE
WHEN ISNULL(tpc.LAST_MODIFIED_DATE,'31-DEC-1900') < tcat.LAST_MODIFIED_DATE THEN tcat.LAST_MODIFIED_DATE
ELSE tcat.LAST_MODIFIED_DATE
END LAST_MODIFIED_DATE, tcat.TAXONOMY_VERSION,CASE WHEN COALESCE(tpc.TAXONOMY_DEFAULT,tcat.TAXONOMY_DEFAULT) = 0 AND tcat.TAXONOMY_DEFAULT =1 THEN tcat.TAXONOMY_DEFAULT
ELSE COALESCE(tpc.TAXONOMY_DEFAULT,tcat.TAXONOMY_DEFAULT)
END TAXONOMY_DEFAULT,tcat.CAT_TAG_ID,tcat.CAT_SW_PRODUCT_ID,tcat.CAT_TAXONOMY2012_ID,
Possibly Referenced Tables/Views: