Hi folks, I'm having trouble getting a CASE WHEN statement working as I expect in a saved search, as a test-bed for running a formula. One extracts a string from the comments field in a customer record, {customer.comments} and the other is based on the {today} tag. We have several customers for whom we do production runs only on a specific day, captured in the comments. Here are the two strings I'm trying to get to match: SUBSTR(REGEXP_SUBSTR ({customer.comments}, 'roast monday|roast tuesday|roast wednesday|roast thursday|roast friday', 1, 1, 'i'), 7) -this returns one of five weekdays, depending on when the production run is supposed to happen -in my test search, this returns 'Tuesday' TO_CHAR({today}+5, 'Day') -since today is Wednesday, this returns 'Tuesday' Then I used a CASE WHEN statement to see if they match: CASE WHEN INITCAP(TO_CHAR({today}+5, 'Day')) = SUBSTR(REGEXP_SUBSTR ({customer.comments}, 'roast monday|roast tuesday|roast wednesday|roast thursday|roast friday', 1, 1, 'i'), 7) THEN '1' ELSE '0' END And finally I've attached the results of the search. The first two columns in the screenshot are the first two formulae, the third is the CASE WHEN statement where I expect a '1'. I've been getting a '0' no matter what I play with - I've wrapped them in TRIM functions, wrapped the TO_CHAR in INITCAP so I'm sure it's returning a character value... any ideas on this?

Does the 'today' - Day function bring back the day with Capitalised first letter days?

Yes, if 'Day'. And So does the SUBSTR

Did you try with formula text ?

Maybe cast both of them to the same type to be sure?

All three are formula text... I'll try the opposite but that won't work for the end solution, which will be a CASE WHEN statement with 7 THEN statements (one for each day of the week)

Using CAST(XXXX, as VARCHAR) XXXX being your query - this would then make sure they are the same type for both queries. I know TO_CHAR is converting to a string but I've had issues with that type of thing before

Brilliant thanks Nick, I thought that might have something to do with it... about to pop into a meeting but I'll try that and report back

I am wondering if LIKE would have worked

I tried that too and I suspect it would, but the issue was the output datatype, as the CAST() function made it work as expected, using = in the CASE WHEN statement