Subject Re: [firebird-support] How can I correctly write this WHERE
Author Svein Erling Tysvær
Post date 2018-06-19T11:54:31Z
Hi Bhavbhuti!

According to https://firebirdsql.org/refdocs/langrefupd15-case.html

this is how case is written:

CASE <expression>
   WHEN <exp1> THEN result1
   WHEN <exp2> THEN result2
   ...
   [ELSE defaultresult]
END
   
and definitely not 

CASE <expression>
   WHEN <exp1> THEN comparison
   WHEN <exp2> THEN comparison2
   ...
   [ELSE defaultcomparison]
END

If you insist on using this construct, you have to modify to something like:
WHERE 1 = CASE WHEN TRIM(vwTA.cNameCity01) = 'Balance Sheet'
THEN case when CAST(tSI.tDt AS DATE) < '2018-04-01' then 1 else 0 end
ELSE case when CAST(tSI.tDt AS DATE) BETWEEN '2018-03-01' AND '2018-03-31' then 1 else 0 END

but it is - of course - better to use things as they are intended to be used, e.g. directly in the SQL like

WHERE CAST(tSI.tDt AS DATE) < '2018-04-01' AND
      (TRIM(vwTA.cNameCity01) = 'Balance Sheet' OR
       CAST(tSI.tDt AS DATE) BETWEEN '2018-03-01' AND '2018-03-31')

2018-06-19 10:25 GMT+02:00 venussoftop@... [firebird-support] <firebird-support@yahoogroups.com>:


Hi all

I am trying to write a conditional WHERE clause but it fails.  Please suggest a correct way to write this

WHERE CASE WHEN TRIM(vwTA.cNameCity01) = 'Balance Sheet'
THEN CAST(tSI.tDt AS DATE) < '2018-04-01'
ELSE CAST(tSI.tDt AS DATE) BETWEEN '2018-03-01' AND '2018-03-31' END


vwTA and tSI are all joined into the query and the error I receive is


Message: isc_dsql_prepare failed

SQL Message : -104
Invalid token

Engine Code    : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 35, column 29
<

This is the < in the THEN clause


Please advise


Kind regards

Bhavbhuti