SQL Query to Determine a Master Catalog ID

Working on a project recently I found the need to determine the master catalog for a list of stores within a WebSphere Commerce database. Seeing as a WebSphere Commerce instance can contain a variety of stores, ranging from your traditional Consumer Direct model to a more extensible Extended Site model, I generated the following query which can be run against the WebSphere Commerce database to properly determine the master catalog identifier for each store.

The query below will include the, if applicable, the StoreGroup1, NullEntity, and ExtendedSites Hub stores, as containing a NULL master catalog. For a query that can filter out NULL master catalog references, please see the additional query below.

SELECT
	S.STOREENT_ID,
	CASE
		WHEN (SELECT CATALOG_ID FROM CATALOG WHERE IDENTIFIER = S.IDENTIFIER) IS NOT NULL THEN (
			SELECT CATALOG_ID
			FROM CATALOG 
			WHERE IDENTIFIER = S.IDENTIFIER
		) ELSE (
			SELECT C.CATALOG_ID
			FROM STOREENT AS E
			JOIN STOREREL AS R ON (R.STORE_ID = E.STOREENT_ID)
			JOIN STORECAT AS C ON (C.STOREENT_ID = R.RELATEDSTORE_ID)
			WHERE R.STRELTYP_ID = -4
				AND R.RELATEDSTORE_ID NOT IN (E.STOREENT_ID)
				AND E.STOREENT_ID = S.STOREENT_ID
		)
	END AS CATALOG_ID
FROM STOREENT AS S
ORDER BY STOREENT_ID, CATALOG_ID
;

If you want to eliminate the NULL master catalog references, you can run the SQL query as noted below to filter out stores that don’t have an associated master catalog. This query leverages the DB2 WITH statement.

WITH STORE_MASTERCATALOG (STOREENT_ID, MASTERCATALOG_ID) AS (
	SELECT
		S.STOREENT_ID,
		CASE
			WHEN (SELECT CATALOG_ID FROM CATALOG WHERE IDENTIFIER = S.IDENTIFIER) IS NOT NULL THEN (
				SELECT CATALOG_ID
				FROM CATALOG 
				WHERE IDENTIFIER = S.IDENTIFIER
			) ELSE (
				SELECT C.CATALOG_ID
				FROM STOREENT AS E
				JOIN STOREREL AS R ON (R.STORE_ID = E.STOREENT_ID)
				JOIN STORECAT AS C ON (C.STOREENT_ID = R.RELATEDSTORE_ID)
				WHERE R.STRELTYP_ID = -4
					AND R.RELATEDSTORE_ID NOT IN (E.STOREENT_ID)
					AND E.STOREENT_ID = S.STOREENT_ID
			)
		END AS CATALOG_ID
	FROM STOREENT AS S
	ORDER BY STOREENT_ID, CATALOG_ID
)
SELECT
	STOREENT_ID, 
	MASTERCATALOG_ID 
FROM STORE_MASTERCATALOG
WHERE MASTERCATALOG_ID IS NOT NULL
;