Long time pending post :)
Here are few queries, which are used commonly in Catalog Subsystem tables,
Query to fetch all the Categories in a Sales Catalog:
----------------------------------------------------------------------------
SELECT c.catgroup_id, c.identifier, cd.name
FROM catgroup c, catgrpdesc cd, catgrprel cr
WHERE c.catgroup_id = cd.catgroup_id
and (cr.catgroup_id_parent = c.catgroup_id or cr.catgroup_id_child = c.catgroup_id)
AND cr.catalog_id = (select s.catalog_id from storedefcat s, storeent se where s.storeent_id = se.storeent_id and se.identifier = 'aurora')
union
SELECT c.catgroup_id, c.identifier, cd.name
FROM catgroup c, cattogrp ctg, catgrpdesc cd
WHERE c.catgroup_id = ctg.catgroup_id
and C.CATGROUP_ID = cd.catgroup_id
AND ctg.catalog_id = (select s.catalog_id from storedefcat s, storeent se where s.storeent_id = se.storeent_id and se.identifier = 'aurora');
Query to fetch Catentry, their Categories in a Sales Catalog:
------------------------------------------------------------------------------
SELECT ce.catentry_id,
ce.partnumber,
CE.CATENTTYPE_ID,
ced.published published,
ce.buyable,
ce.mfname,
ce.mfpartnumber,
ced.name,
ced.shortdescription shortdescription,
cg.catgroup_id,
cg.identifier,
cger.catalog_id
FROM catentry ce, catentdesc ced, catgpenrel cger, catgroup cg
WHERE ce.catentry_id = ced.catentry_id
AND ce.catentry_id = cger.catentry_id
and ce.catentry_id = ced.catentry_id
AND cger.catgroup_id = cg.catgroup_id
AND cg.markfordelete = 0
AND ced.language_id = -1
--AND ced.published = 1
AND ce.markfordelete = 0
AND cger.catalog_id =
(SELECT sdc.catalog_id
FROM storeent se, storedefcat sdc
WHERE se.storeent_id = sdc.storeent_id
AND se.identifier = 'aurora')
order by ce.catentry_id;
Query to fetch attributes of a given Partnumber:
--------------------------------------------------------------------------------------
SELECT ce.catentry_id,
ce.partnumber,
a.identifier AS attridentifier,
(select name from attrdesc ad where ad.attr_id = cea.attr_id AND ad.attr_id = cea.attr_id) as attrname,
(SELECT DECODE (a.attrtype_id, 'STRING', stringvalue, 'INTEGER', integervalue) FROM attrvaldesc avd WHERE avd.attrval_id = cea.attrval_id AND avd.attr_id = cea.attr_id) AS attrvalue
FROM catentry ce, catentryattr cea, attr a
WHERE ce.catentry_id = cea.catentry_id
AND cea.attr_id = a.attr_id
AND ce.partnumber = 'MotoG'
ORDER BY a.identifier;
Fetch Attributes and its values for a given Partnumber:
------------------------------------------------------
SELECT ce.catentry_id,
ce.partnumber,
a.identifier AS attridentifier,
(select name from attrdesc ad where ad.attr_id = cea.attr_id AND ad.attr_id = cea.attr_id) as attrname,
(SELECT DECODE (a.attrtype_id, 'STRING', stringvalue, 'INTEGER', integervalue) FROM attrvaldesc avd WHERE avd.attrval_id = cea.attrval_id AND avd.attr_id = cea.attr_id) AS attrvalue,
(SELECT identifier FROM attrval WHERE attr_id = a.attr_id AND attrval_id = cea.attrval_id) AS attrvalidentifier,
ce.optcounter AS catentry_counter,
cea.optcounter AS catentryattr_optcounter,
a.optcounter AS attr_counter,
(SELECT optcounter FROM attrvaldesc avd WHERE avd.attrval_id = cea.attrval_id AND avd.attr_id = cea.attr_id) AS attrvalue_optcounter
FROM catentry ce, catentryattr cea, attr a
WHERE ce.catentry_id = cea.catentry_id
AND cea.attr_id = a.attr_id
AND ce.partnumber = 'ReebokABC'
ORDER BY a.identifier;
Query to fetch catentry, their categories, attributes and their values in the sales catalog:
---------------------------------------------------------------------------------------------------
SELECT ce.catentry_id,
ce.partnumber,
ced.name,
ced.published published,
ce.mfname,
ce.mfpartnumber,
ced.shortdescription shortdescription,
cger.catalog_id,
a.identifier AS attridentifier,
(SELECT DECODE (a.attrtype_id, 'STRING', stringvalue, 'INTEGER', integervalue) FROM attrvaldesc avd WHERE avd.attrval_id = cea.attrval_id AND avd.attr_id = cea.attr_id) AS attrvalue
FROM catentry ce, catentdesc ced, catgpenrel cger, catgroup cg, catentryattr cea, attr a
WHERE ce.catentry_id = ced.catentry_id
AND ce.catentry_id = cea.catentry_id
AND ce.catentry_id = cger.catentry_id
AND cger.catgroup_id = cg.catgroup_id
AND cea.attr_id = a.attr_id
AND cg.markfordelete = 0
AND ced.language_id = -1
AND ced.published = 1
AND ce.markfordelete = 0
AND cger.catalog_id =
(SELECT sdc.catalog_id
FROM storeent se, storedefcat sdc
WHERE se.storeent_id = sdc.storeent_id
AND se.identifier = 'aurora')
order by ce.catentry_id;
Query to fetch merchandising association of a given Partnumber:
--------------------------------------------------------------------------------------------
SELECT c1.partnumber AS sourcepartnumber, c2.partnumber AS targetpartnumber, massoctype_id, m.Rank, store_id
FROM massoccece m, catentry c1, catentry c2
WHERE m.catentry_id_from = c1.catentry_id
AND m.catentry_id_to = c2.catentry_id
AND c1.partnumber = 'MotoG';
Query to fetch attachments associated with for a given Partnumber:
----------------------------------------------------------------------------------------------
SELECT c.partnumber, atg.identifier AS attachmentId,
(SELECT atchastpath FROM atchast WHERE atchtgt_id = atg.atchtgt_id) AS attachmentPath
FROM atchrel a, atchtgt atg, catentry c
WHERE a.bigintobject_id = c.catentry_id
AND a.atchtgt_id = atg.atchtgt_id
AND c.partnumber = 'MotoG';
