How to write direct flexible search query to retrieve the classification atrributes values for a product

[Former Member]

Im trying to get all classification attribute values for a particular product through hac(flexible search query).Can anyone help on this?

[geffchang · Accepted solution]

This should work:

 SELECT 
     {p.code}, {cat.id}, {cv.version}, {pf.qualifier}, {pf.stringValue}
 FROM 
     {ProductFeature as pf 
     JOIN Product as p ON {p.pk}={pf.product} 
     JOIN CatalogVersion as cv ON {cv.pk}={p.catalogVersion}
     JOIN Catalog as cat ON {cat.pk}={cv.catalog}} 
 WHERE 
     {cat.id} LIKE 'Default' 
     AND {cv.version} LIKE 'Staged' 
     AND {p.code} LIKE 'PRODUCT_CODE' 

[Former Member · Accepted solution]

Hi

Thanks for the reply.I'm getting the results but with pk's of attribute values. So i tried to join ClassificationAttributeValue table as belowbut getting the exception as... Exception message: SAP DBTech JDBC: [266]: inconsistent datatype: lob type comparison: line 1 col 187 (at pos 186)....Could you please help if any issue with my query.

SELECT {p.code}, {cat.id}, {cv.version}, {pf.qualifier}, {pf.stringValue},{cav.name} FROM {ClassificationAttributeValue as cav JOIN ProductFeature as pf ON {pf.stringValue}={cav.pk} JOIN Product as p ON {p.pk}={pf.product} JOIN CatalogVersion as cv ON {cv.pk}={p.catalogVersion} JOIN Catalog as cat ON {cat.pk}={cv.catalog}}

WHERE {cat.id} LIKE 'Catalog' AND {cv.version} LIKE 'Staged' AND {p.code} LIKE 'PRODUCT_CODE'