The reason the first query failed, Bhavbhuti, was because I forgot that
nested CASE also needs nested END, so adding END to the end of the query
should work on Fb 2.5 (and thanks Mark, for showing me a part of
Firebird 3 that I didn't know of). Still, even though it should work, it
will be slow and is a worse solution than the other I suggested.
One of the reasons the other query is slow, is because CAST and TRIM
means that no index can be used (well, except expression index). If
tSI.tDt is defined as DATE or TIMESTAMP, then you could try
WHERE tSI.tDt < '2018-04-01' AND
(TRIM(vwTA.cNameCity01) = 'Balance Sheet' OR /* Also remove TRIM
if it isn't needed */
tSI.tDt >= '2018-03-01')
although my guess is that this will either make no difference or be
slower and that the cause of your slowness is in parts of the query that
you haven't shown us.
HTH,
Set
Den 20.06.2018 13:38, skrev Venus Software Operations
venussof...@gmail.com [firebird-support]:
Thanks SET. I tried both your examples, the CASE WITH fails for me
because I am still using FB2.5. The other one works but the query has
slowed down considerably to 3-5 minutes. Of course this is a complex
query already using a CTE and then the query itself joins a few tables
and a View.
The field of interest cNameCity01 is from a View in FireBird and this
is slowing down the query. Even if I do not put the WHERE condition
and I just JOIN the View the query slows down. I have tried to replace
this character field with it's ID in case strings are slow but no joy.
I had also tried to move the JOIN of the View from the main query into
the CTE (as character as well as integer ID) as I just needed this one
field, though the SELECT of the CTE itself with this JOIN is very fast
but as a whole query it remains slow.
I have tried to add index for fields in JOINs and ORDER BYs but no
difference
Any suggestions?
Thanks and regards
Bhavbhuti
On 19/06/2018 05:24 pm, Svein Erling Tysvær setys...@gmail.com
[firebird-support] wrote:
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
<mailto:venussof...@gmail.com> [firebird-support]
<firebird-support@yahoogroups.com
<mailto: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
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient>
Virus-free. www.avg.com
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
--
Thanking you.
Yours Faithfully,
For Venus Software Operations
----
Mr. Bhavbhuti Nathwani
___________________________________________
Softwares for Indian Businesses at:http://www.venussoftop.com
venussof...@gmail.com
venussof...@mail.ru
___________________________________________
Please note: We reserve complete rights for policy changes in the future and
the same will be applicable immediately as and when made. Attachments may get
corrupted before reaching you, in such a situation please let us know and we
will resend you the same at the earliest. We do not take any responsibility
for data loss of any type and kind. Data safety remains the sole the
responsibility of the users of our softwares.
___________________________________________
Internet email confidentiality:
This message may contain information that may be privileged or confidential.
If you are not the addressee nor are you responsible for the delivery of the
message to the addressee indicated in this email, then you may not copy or
deliver this email to anyone and you should notify the sender by reply email
and then destroy this message.
Please reply email immediately to this message with REMOVE in the subject, if
you or your employer do not consent to email of this kind.
Opinions, conclusions and other information in this message that do not relate
to the official business of my firm shall be understood as neither given nor
endorsed by my company.
