cancel
Showing results for 
Search instead for 
Did you mean: 

how to retrieve products under a particular category using flexible search querry?

Former Member
0 Kudos

hello, I have created a super category called SAP ERP under that i have some products. Now how to retrieve only those products that are under category SAP ERP using flexible search. Can any one please help me with this. please share the querry. ` Thanks in advance

Accepted Solutions (1)

Accepted Solutions (1)

Former Member

Hi Sriharsha!

Try a query similar to the following:

 SELECT {p.pk},{p.name} FROM {Product as p}, {CategoryProductRelation as cpr}, {Category as c}, {CatalogVersion as cv}, {Catalog as cat} WHERE {p.pk}={cpr.target} AND {c.pk}={cpr.source} AND {c.catalogVersion} AND {p.catalogversion}={cv.PK} AND {cv.catalog}={cat.PK} AND {cat:id}='electronicsProductCatalog' AND {cv.version}='Online' AND {c.code}='SAP_ERP'

You need to specify the catalog (see: {cat:id}='YOURCATALOG') and catalog version (see: {cv.version}='Online') if you want to specify the catalog version. (Remove these if you want to show all products in all catalog versions in this category.

Then, you simply have to change the category code (see: {c.code}='SAP_ERP') to reflect the category to want to search with.

Also, specify what you want to return. I'm only returning the products' PK and name (see: SELECT {p.pk},{p.name})

Best regards,

Niko

Former Member
0 Kudos

Thank you ,

if i use that code it is throwing exception " data type of expression is not boolean". actually i have created Course which extends product. so i want to retrive all the courses that i have created which are exclusively under the category SAP ERP. can you please help me with this.

Former Member
0 Kudos

Why don't you just take this category and get it's product list then?

0 Kudos

You can use this query it will work

SELECT {p.pk},{p.name} FROM {Product as p}, {CategoryProductRelation as cpr}, {Category as c}, {CatalogVersion as cv}, {Catalog as cat} WHERE {p.pk}={cpr.target} AND {c.pk}={cpr.source} AND {p.catalogversion}={cv.PK} AND {cv.catalog}={cat.PK} AND {cat:id}='ProductCatalog' AND {cv.version}='Online' AND {c.code}='SAP_ERP'

Answers (0)