Flexible Search Query for Lowest and Highest Price of Variant Product

[former_member666604]

Hi Experts ,

How to write a flexible search query, that only returns the variant with the lowest price (row) and highest price(row).

so basically need to write two flexible search query ,one for lowest price(row) and other one for highest price(row).

can anyone help me on this.

Thanks in advance.

Regards,

[former_member632827]

Hi Satish,

Here, europe1Prices is the dynamic attribute we can't add direct relation on PriceRow with the product. Europe1Prices's handler fetches price rows based on multiple constraints such as Product Id, Product matcher, User matcher, the currency of the current site, and user group, etc.

https://help.sap.com/viewer/d0224eca81e249cb821f2cdf45a82ace/1905/en-US/961b19dfdcd34eff8c6659e0fad8...

The best way to you get the product first then find the max and min price values using stream java API from the price rows.

stream() .max

stream() .min

If you are working with purely OOTB price rows data, it has guaranty productId relation with the product. So you can use below query

Select MAX({pr.price}),MIN({pr.price}) from {VariantProduct as vp JOIN PriceRow as pr ON {pr:productId}={vp:code}} Where {vp:code}='<product code>'

[crescenzorega]

Hi,

try using this query

SELECT
   * 
FROM
   (
      {{ 
      SELECT
         MAX({pr1.price}) 
      FROM
         {PriceRow AS pr1} }} 
      UNION
      {{ 
      SELECT
         MIN({pr2.price}) 
      FROM
         {PriceRow AS pr2} }}
   )
   uniontable