Subject Re: [firebird-support] How can I correctly write this WHERE
Author setysvar
Post date 2018-06-20T21:30:24Z
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 venussoftop@... [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 setysvar@... [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 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






Virus-free. www.avg.com 

-- 


Thanking you.

Yours Faithfully,
For Venus Software Operations
----
Mr. Bhavbhuti Nathwani
___________________________________________
Softwares for Indian Businesses at: http://www.venussoftop.com

venussoftop@...
venussoftop@...
___________________________________________

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.