SAP Commerce (Hybris) has its own query language called Flexible Search. Flexible Search has a SQL-like syntax, it converts query to SQL query. The most significant advantage of Flexible Search is its database independence. I am going to share some advanced query hints in this article.

Flexible query will be converted to the query language of your DB. There are some minor differences between DB’s SQL. For example, AzureSQL has LEN(columnName) function but MySQL has LENGTH(columnName) as equivalent. So, you should consider your DB type while writing Flexible Queries.

Following samples are for SAP CC AzureSQL Database. Some of them may fail on your local MySQL database.

Table of contents

• Date
  • DateTime Format
  • Timezone conversion in Flexible Query Convert
  • DATEDIFF
• Nested Select
• Union
• CAST

Date

Some date functions of Flexible Search Query:

DateTime Format

You can format date by using FORMAT function

SELECT {p.code}, FORMAT({p.modifiedTime}, 'yyyy-MM-dd HH:mm') AS update_time
FROM {Product as p}

Timezone conversion in Flexible Query Convert

Convert UTC time to UTC+3 (Turkey Time) in Flexible Query

SELECT {o.code}, DATEADD(HOUR, 3, CONVERT(DATETIME, {o.creationTime}) AT TIME ZONE 'UTC') AS ConvertedTime
FROM {Order AS o}

My server timezone is UTC. So, flexible query always returns date in UTC format. I need to convert it to UTC+3.

CONVERT function converts creationTime to DATETIME format in UTC time zone. DATEADD function adds 3 hours to convert it to UTC+3.

DATEDIFF

Get cronjobs that run for more than 10 seconds.

SELECT {c.code}, DATEDIFF(SECOND, {startTime}, {endTime}) as "execution time seconds", 
DATEDIFF(MINUTE, {startTime}, {endTime}) as "execution time minute", 
{startTime} as "start time" 
FROM {CronJobHistory as ch 
JOIN Cronjob as c on {c.pk} = {ch.cronjob}} 
WHERE {endTime} IS NOT NULL AND {startTime} IS NOT NULL AND
DATEDIFF(SECOND, {startTime}, {endTime}) > 10
ORDER BY {c.code}, {startTime}

Above query gets difference of start and end time of CronJobHistory

DATEDIFF function can have different parameters like SECOND, MINUTE, HOUR

Nested Select

Fetch coupons that have only published promotions.

SELECT {c.pk}, {couponId}
FROM {AbstractCoupon AS c}
WHERE NOT EXISTS (
    {{ 
        SELECT 1 
        FROM {PromotionSourceRule AS p join EnumerationValue as st1 on {st1.pk} = {p.status}} 
        WHERE {p.code} = {c.promotionId} 
        AND {st1.code} = 'PUBLISHED'
    }}
)

Using select in WHERE statement is bad for performance, using it on production may cause performance issues.

Union

You can merge different select queries by using UNION. Below sample Category and its sub type ProductHierarchyCategory fetched together. This query is used for Solr Update Indexer Query.

SELECT tbl.pk FROM 
(
{{
 SELECT {PK} FROM {Category! as c} WHERE {c.modifiedtime} >= '2025-01-01'
}} 

UNION 

{{ 
 SELECT {PK} FROM {ProductHierarchyCategory as pc} WHERE {pc.modifiedtime} >= '2025-01-01' 
}}
) tbl

Flexible search union has some syntax difference than SQL. Please notice curly braces usage as {{ SELECT ...}} of each select statement

CAST

Cast binary to VARCHAR. Order appliedCouponCodes field is CollectionType. CollectionType fields stores data as binary and it shows value like [B@1133ddf9 in HAC. You can convert it to String by using CAST and VARCHAR
Result will be ¬ísrjava.util.HashSetºD…•–¸·4xpw?@
t COUPON1 You can extract coupon code from this String.

SELECT {o.code}, {o.headerPromotionActionId}, 
CAST({o.appliedCouponCodes} AS VARCHAR(4000)) as appliedCoupon 
FROM {Order as o
JOIN PromotionResult as pr on {pr.order}={o.pk}
JOIN AbstractPromotion as ap on {ap.pk}={pr.promotion}}