We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

SQL code for filter tables in Firebird [closed]

asked 2020-10-28 22:00:53 +0200

paturdc gravatar image

updated 2021-06-02 19:56:52 +0200

Alex Kemp gravatar image

I'm used to using this syntax when employing filter tables to forms in HSQLDB:

     ((UPPER("FirstName")    LIKE '%' || UPPER (:Var_FN) || '%' ) OR (:Var_FN  IS NULL))

I cannot get this to work in FireBird, and it seems to have to do with the second part in the WHERE clasue: "OR (:Var_FN IS NULL))"

What's the alternative? I need a blank field to select all records from the filtered table.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2021-06-02 19:57:01.924983

2 Answers

Sort by » oldest newest most voted

answered 2020-10-28 23:12:02 +0200

frofa gravatar image

updated 2020-10-30 02:45:56 +0200

I think the user parameter value must be explicitly CAST as VARCHAR: e.g.

 ((UPPER("FirstName") LIKE '%' || UPPER (CAST(:Var_FN) AS VARCHAR(10)) || '%' )

UPDATE: Below-attached is a sample FB Base file to show a query similar to yours using the above code-technique. To test just run the query qInput_via_user_parameter_UPPERCASE with just ONE upper or lower-case alphabetic letters. Not sure why the above-quoted code didn't work, but let us know if you have success (or not) with this approach anyway.


edit flag offensive delete link more


I tried this a bit quickly, but couldn't make it work. Using LO Base 6.4 on Windows 10.

paturdc gravatar imagepaturdc ( 2020-10-29 22:27:55 +0200 )edit


Your original code works fine with a minor change (also only need to cast for NULL):

   "FirstName" LIKE '%' || :Var_FN || '%' 
Ratslinger gravatar imageRatslinger ( 2020-10-30 03:27:45 +0200 )edit

@Ratslinger, Thanks for that refinement. Glad you are back on deck.

frofa gravatar imagefrofa ( 2020-10-30 05:40:50 +0200 )edit

It looks I was too hasty the first time around, and made some syntax error. I can now get the user input via parameter to work on my own file :)

Filtering directly from the filter table looks much cleaner to my eyes, but I cannot make it work for NULL. Doesn't matter, I'll go with user parameters in my own work.

with just ONE upper or lower-case alphabetic letters.

Not sure what you mean by this, it works with one or many or a blank field.

paturdc gravatar imagepaturdc ( 2020-10-30 19:27:32 +0200 )edit

answered 2020-10-29 00:16:24 +0200

Ratslinger gravatar image


Please see my answer and comments in this post:


edit flag offensive delete link more


Thank you Ratslinger.

While this solution works, it seems I have to type as many characters into COALESCE as the user might conceivably wish to use for his search term? Considering I have VARCHAR(100) and the user might want to search an arbitrary length of that field - this seems extremely clunky relative to "OR * IS NULL."

Is there something I'm not understanding correctly?

paturdc gravatar imagepaturdc ( 2020-10-29 08:14:36 +0200 )edit


No, you have understood it correctly. What you may not have understood is that the situation is a bug (don't believe it has been reported from the linked post) and the solution is simply a work around.

You example defines a first name and it seems 100 characters is overkill for this. So even if the field is 100 characters, if entry does not exceed 25, then only 25 are needed.

Ratslinger gravatar imageRatslinger ( 2020-10-29 16:27:59 +0200 )edit

I have in several tables a "Description" field, which in HSQLDB is MEMO, and I think I'll convert to VARCHAR(100). But I need to be able to search it properly. If this is a workaround, I can certainly live with having 20 or 30 characters max until the bug gets fixed some time in the future.

Thanks for the explanation.

paturdc gravatar imagepaturdc ( 2020-10-29 22:23:36 +0200 )edit


Code from @frofa works (see sample & my comment). Please change accepted answer to that one.

Ratslinger gravatar imageRatslinger ( 2020-10-30 03:29:36 +0200 )edit

Question Tools

1 follower


Asked: 2020-10-28 22:00:53 +0200

Seen: 189 times

Last updated: Oct 30 '20