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 venussof...@gmail.com [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
>
>
>
>
>
>
