Flexible Search Query Builder
Build and validate SAP Commerce FlexibleSearch queries
Query Builder
Generated Query
Select a type and add conditions to generate a query.
Help & Tips
FlexibleSearch Syntax
- Use
{field}for field references - Use
{pk}for primary key - String values must be quoted with single quotes
- Date values can use relative syntax like 'NOW-30d'
Common Operators
- equals: Exact match
- like: Pattern matching with % wildcards
- in: Match any value in a list
- between: Range matching
- isNull/isNotNull: Null value checks
Performance Tips
- Use indexed fields in WHERE conditions
- Limit result sets with LIMIT clause
- Avoid LIKE with leading wildcards
- Use appropriate data types for comparisons
Flexible Search Query Builder
Build and validate SAP Commerce FlexibleSearch queries
Features
- Visual builder for SAP Commerce FlexibleSearch queries: select item type, choose attributes for SELECT/WHERE/ORDER BY, build joins via reference attributes, all without writing SQL
- Pre-loaded SAP item types: Product, User, Order, Category, Customer, Address, etc. — with their canonical attributes (code, name, creationTime, etc.) and types (String, Number, Date, Boolean, Collection)
- Attribute type-aware operators: String fields offer LIKE/IS NULL/IN; Number fields offer comparison and BETWEEN; Date fields offer date pickers with range support; Boolean offers IS TRUE/FALSE
- Live SQL output: the generated FlexibleSearch query appears below the builder, updating with each click — you can paste straight into HAC, hybris admin, or a Java service
- Pagination + ordering controls: ORDER BY with multi-column support, LIMIT/OFFSET for paged queries, with the SAP Commerce-specific quirks (no LIMIT in FlexibleSearch — uses page params instead)
- Multi-language attribute support: ?language=$language placeholders for locale-aware text columns (name, description) — generated correctly per SAP's i18n conventions
- Validation pass: undefined attribute references, type mismatches in WHERE conditions, missing JOIN aliases — all flagged before you paste the query into HAC
- Pure client-side: type metadata and query generation run in your browser, no upload. Safe for queries against confidential schemas or production-data catalog names
How to use
- Pick the item type you want to query (Product, User, Order, etc.) from the dropdown.
- Choose attributes for SELECT (what columns appear in results) by ticking them in the attributes panel.
- Add WHERE conditions: pick attribute, operator, value — repeat for AND/OR composition.
- Add JOINs if you need related data: pick a reference attribute (catalogVersion, user, etc.) and the joined type's attributes appear.
- Set ORDER BY for sorting and pagination params for paged results.
- Copy the generated FlexibleSearch query and paste into SAP Commerce HAC (hac.zul/flexsearch) or your Java service.
Tips & Best Practices
- Use the validation panel to catch attribute typos before pasting into HAC; HAC will fail with a generic syntax error otherwise.
- For locale-aware queries, always add ?language=$language to WHERE/SELECT on String localizable attributes.
- Pagination is via setStart/setCount in Java code, not in the FlexibleSearch text — design your service accordingly.
- For ORDER BY on multi-language fields, the platform sorts by the system default locale; for locale-specific sorting, sort in your Java layer after fetching.
- Test the generated query in HAC's flexsearch console (hac/console.zul) before deploying; HAC catches some errors the builder doesn't.
FAQ
What's the difference between FlexibleSearch and SQL?
FlexibleSearch is SAP Commerce's SQL-like query language: uses item-type references ({Product}) and attribute placeholders ({code}) instead of raw table/column names. The platform translates FlexibleSearch into the underlying DB SQL at query time, applying type-system rules. SQL is database-specific; FlexibleSearch is portable across PostgreSQL/MySQL/Oracle behind SAP Commerce.
Why are some attributes marked as required?
Required attributes are non-nullable in the SAP type system (e.g. Product.code, Order.code, User.uid) — every record must have one. Marking them required helps you avoid WHERE conditions that would never match (WHERE code IS NULL on a required field is dead).
How does the builder handle multi-language attributes?
String attributes with localizable=true (name, description) require a ?language=$language placeholder in the WHERE clause; otherwise FlexibleSearch returns the value in the system default locale. The builder emits the placeholder automatically when you reference these attributes.
Why is LIMIT/OFFSET not in my generated query?
FlexibleSearch doesn't support SQL's LIMIT/OFFSET clauses directly. The platform pagination uses query.setStart(N) and query.setCount(M) on the FlexibleSearchQuery Java object. The generated query is the WHERE/ORDER BY part; pagination params are passed alongside via Java.
Can I include joins to extension item types?
The pre-loaded types are core SAP Commerce. For extension types (custom MyCompanyProduct extends Product), the builder doesn't know about them automatically. Workaround: type the attribute name in the WHERE input directly — the validator won't auto-check it but the generated SQL will work if the type exists.
How are subqueries handled?
Currently not supported in the visual builder. For SELECT ... WHERE code IN (SELECT ...) patterns, build the inner query first, copy it, and edit the outer query manually in the generated output pane.
Is my query sent to SAP or your servers?
No. The builder runs entirely in your browser; the generated query string is yours to paste into HAC. We don't observe, log, or telemeter the queries you build.
What if my SAP version is older?
The pre-loaded types reflect SAP Commerce 2105+ schema. Older versions (1811, 1905) may not have all attributes — verify by checking the type via items.xml in your project. The builder doesn't auto-detect SAP version.