IBM WCS ( Websphere Commerce ) - SQLs to retrieve Attributes


Retrieve color of all kitchen appliances along with their name.


1. There is catentry type for Kitchen Appliances and all kitchen appliances are mapped with this type.
2. Color has been stored as an attribute. 

Tables Referenced 

catenttype -> catentry -> attr -> attrdesc -> catentryattr -> attrval -> attrvaldesc

1. catenttype -> Catalog Entry Type Master table
2. catentry -> Catalog Entry Table
3. attr -> Attribute
4. attrdesc -> Attribute Description
5. catentryattr -> Relationship between catentry, attribute and attribute value.
6. attrval -> Attribute Value
7. attrvaldesc -> Attribute Value Desciption


1. Get catentry type id for "Kitchen Appliances"

select catenttype_id from catenttype where description = 'Kitchen Applicances';

2. Get catentries belonging to this catentry type

select catentry_id from catentry where catenttype_id = <Retrieved above>

3. Get Attr id for attribute color

select attr_id from attrdesc where Name='Color'

4. Get attr value ids for color attribute for these catentries Ids

select attrval_id from catentryattr where catentry_id in ( <Retrieved  in 2 >) and attr_id=<Retrieved in 3>

5. Get attr value name for these attrval ids

Select value from attrvaldesc where attrval_id in < Retrieved in 5 >