BDNA Data Platform 5.4 July 2017 (SQL Server)
CAT_SW_TAXONOMY2012 - Bridge table between CAT_SW_PRODUCT and TAXONOMY2012

Legend:
Primary key columns
Columns with indexes
Implied relationships
Excluded column relationships
< n > number of related tables
 
Column Type Size Nulls Auto Default Children Parents Comments
CAT_SW_TAXONOMY2012_ID numeric 20  √  null CAT_SW_TAXONOMY2012 table's unique identifier
CREATE_DATE datetime 16,3  √  null The date when row was first created
LAST_MODIFIED_DATE datetime 16,3  √  null The date when last modification on the row was made
TAXONOMY_VERSION numeric 20  √  null Taxonomy version, start from 1
TAXONOMY_DEFAULT numeric 20  √  null Identify the current product-category
CAT_TAG_ID numeric 20  √  null CAT_TAG Unique identifier
CAT_SW_PRODUCT_ID numeric 20  √  null Software product's unique identifier
CAT_TAXONOMY2012_ID numeric 20  √  null CAT_TAXONOMY2012 table's unique identifier
TO_BE_DELETED varchar 1  √  null Would this record be deleted? eg: Y
TO_BE_DELETED_ON datetime 16,3  √  null The date when this record would be deleted
DELETE_REASON varchar 500  √  null The reason why the entry is marked to be deleted (Values: Duplicate,Inaccurate)
PRIVATE_FLAG numeric 3  √  null 0 = Not Modified (i.e. original Technopedia entry),1 = Modified Technopedia object, 2 = Proprietary object (those that have negative ids)
IS_PRIVATE varchar 1  √  null y = a private entry, n = original Technopedia entry
PRIVATE_TYPE varchar 500  √  null PRIVATE_TYPE = NULL if PRIVATE_FLAG = 0,PRIVATE_TYPE = Modified Technopedia entry if PRIVATE_FLAG = 1,PRIVATE_TYPE = Proprietary entry if PRIVATE_FLAG = 2
REPLACEMENT_ID numeric 20  √  null Unique identifier of the replacement entry whenever the entry is marked to be deleted for 'Duplicate' reason

Analyzed at Thu Jul 20 12:14 PDT 2017

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:

Confidential and Proprietary to BDNA