WebSphere Commerce 7 | Catalog Subsystem | Queries | Set 1

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';