Question Re: Incorrect Result FlexibleSearch Query in CRM and CX Q&A

Incorrect Result FlexibleSearch Query

Former Member — Thu, 10 Jan 2019 07:51:26 GMT

Hi Experts,

I'm trying to run the below query but it's not giving me the expected result.

SELECT DISTINCT to_char({cst:creationtime},'dd/MM/yyyy') FROM {CsTicket AS cst} WHERE to_char({cst:creationtime},'yyyy/MM/dd') BETWEEN '2019/01/08' AND '2019/01/09'

Can you please help me in finding the issue?

Hybris:6.2 DB: Oracle

Re: Incorrect Result FlexibleSearch Query

Former Member — Thu, 10 Jan 2019 08:17:07 GMT

The data that it fetches from the DB is not in the date range.

Re: Incorrect Result FlexibleSearch Query

arvind-kumar_avinash — Thu, 10 Jan 2019 08:20:55 GMT

Please try this:

 SELECT DISTINCT TO_CHAR({cst:creationtime},'DD/MM/YYYY') FROM {CsTicket AS cst} WHERE {cst:creationtime} BETWEEN TO_DATE('2019/01/08','YYYY/MM/DD') AND TO_DATE('2019/01/09','YYYY/MM/DD')

The reason why your query failed to fetch correct data is because you compared two strings and not two dates for calculating the range.

If you need dates in date format instead of string format, you can further change my query to:

 SELECT DISTINCT TO_DATE({cst:creationtime},'DD/MM/YYYY') FROM {CsTicket AS cst} WHERE {cst:creationtime} BETWEEN TO_DATE('2019/01/08','YYYY/MM/DD') AND TO_DATE('2019/01/09','YYYY/MM/DD')

Re: Incorrect Result FlexibleSearch Query

Former Member — Thu, 10 Jan 2019 09:01:16 GMT

Hi Thanks for the reply. I actually figured it out now.

Another question, when I convert this to FlexibleSearchQuery where the first date is the start date and the second date is end date, Do I need to keep them in quotes?

BETWEEN to_date('?startDate','dd/MM/yyyy') AND to_date('?endDate','dd/MM/yyyy')

BETWEEN to_date(?startDate,'dd/MM/yyyy') AND to_date(?endDate,'dd/MM/yyyy') will work fine?

Re: Incorrect Result FlexibleSearch Query

arvind-kumar_avinash — Thu, 10 Jan 2019 09:18:28 GMT

You are most welcome.

I suggest using the whole query as a string e.g. https://help.hybris.com/1811/hcd/8bc399c186691014b8fce25e96614547.html#topic_owf_ltp_g5