SQL Query to Determine a Master Catalog ID

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
;

Author: daharveyjr

I’m a solution architect responsible for the design, development, implementation, testing, and maintenance of e-commerce operations and applications using the Hybris and WebSphere Commerce product suites and other web technologies such as Java, J2EE/JEE, Spring, PHP, WordPress and more. Twitter | Facebook | LinkedIn

1 thought on “SQL Query to Determine a Master Catalog ID

  1. daharveyjr Post authorReply

    There are obviously multiple methods to retrieve the master catalog, this article focused on the methods described in the InfoCenter article found here: http://pic.dhe.ibm.com/infocenter/wchelp/v7r0m0/topic/com.ibm.commerce.developer.doc/tasks/tsdsearchsetupstructure.htm, meant to help you discover a master catalog id. You could also query against the STORECAT table, and compare it against the catalog table to determine a master catalog, if you wish to go that route. Both are acceptable solutions, my query was meant to truly rely on the store relationships table.

Leave a Reply

Your email address will not be published.