Parameter query with NULL state parameters

[bbater]

Operating system: Windows 10 Pro Version of LibreOffice: Version: 6.2.5.2 (x64) File format: ODB Database engine: Firebird

I want a parameter query to return results even if either or both parameters are not entered by the user. I.e.:

• Parameter1 valid & Parameter2 valid = results based upon both Parameters
• Parameter1 valid & Parameter2 not given = results based upon Parameter1
• Parameter1 not given & Parameter2 valid = results based upon Parameter2
• Parameter1 not given & Parameter2 not given = all results from the tables irrespective of either Parameter

This is my query:

SELECT “Client”.“Client_ID”, “Client”.“FamilyName”, “Client”.“ForeName”, “Status”.“AsylumStatus”, “Status”.“StatusFromDate” FROM “Status”, “Client” WHERE “Status”.“Client_ID” = “Client”.“Client_ID” AND ( LOWER ( “Status”.“AsylumStatus” ) = ( LOWER ( :AsylumStatus ) ) OR ( :AsylumStatus IS NULL ) ) AND “Status”.“StatusFromDate” BETWEEN COALESCE ( :FromDate, ‘2018-01-01’ ) AND CURRENT_DATE ORDER BY “Client”.“FamilyName” ASC

You will notice I have used COALESCE to default to a static FromDate value if none is entered, and this seems to work. I’ve tried using COALESCE also for :AsylumStatus (a VARCHAR datatype) in the same way with a default of ‘a’, but this does not seem to work. What I can’t get my head around is why other combinations of data and non-entries fail. This is what happens in each combination for the above query:

• :AsylumStatus not given & :FromDate given = expected results based upon :FromDate
• :AsylumStatus not given & :FromDate not given = all records, as expected
• :AsylumStatus given & :FromDate given = ERROR “Incorrect type for setString”
• :AsylumStatus given & :FromDate not given = ERROR “Incorrect type for setString”

It appears that providing a value for :AsylumStatus is what is causing the problem. How can I adjust my query to overcome this problem?

[Ratslinger]

Hello,

Have tried a number of scenarios but each time when checking for NULL in combination with possibly a entered value I get the 'setString error. Finally used COALESCE and that worked using what I think may be OK for you (can change character if need be):

SELECT "Client"."Client_ID",
       "Client"."FamilyName",
       "Client"."ForeName",
       "Status"."AsylumStatus",
       "Status"."StatusFromDate"
FROM "Status",
     "Client"
WHERE "Status"."Client_ID" = "Client"."Client_ID"
AND   ((LOWER("Status"."AsylumStatus") = LOWER(:AsylumStatus))
          OR (COALESCE(:AsylumStatus,'*') = '*'))
AND   "Status"."StatusFromDate" BETWEEN COALESCE(:FromDate,'2018-01-01') AND CURRENT_DATE
ORDER BY "Client"."FamilyName" ASC

Edit 2020-01-26:

I am not certain of your data but this works without error. Have tested all scenarios PRIOR to original posting. They all worked without error.

Here is the sample used ------------- NullQuery.odb

Edit #2 2020-01-26:

Have found the error. All testing was done where the Status was a single character. If this is larger than one character the error appears. The can be corrected by modifying the comparison to have one more character than the largest field. For example, if largest Status can be five characters then:

          OR (COALESCE(:AsylumStatus,'******') = '******'))

or use another character or string:

          OR (COALESCE(:AsylumStatus,'aaaaaa') = 'aaaaaa'))

Sorry for the inconvenience.

[bbater]

Many thanks Ratslinger. I realise that without the database you can’t test the query. Your proposed code works as before if no value is entered for ‘AsylumStatus’, whether or not StatusFromDate is given a value. If a valid value for AsylumStatus is entered, your code actually generates a different error ‘-303’, as below.

Error code 1
firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -303
*arithmetic exception, numeric overflow, or string truncation
*string right truncation
*expected length 1, actual 4
caused by
‘isc_dsql_execute’

I noted that using ‘’ and '’ in the COALESCE statement changes the error message line 7 to ‘*expected length 4, actual 7’. I don’t know if this provides any clues as to what’s going on. If unresolved, I’ll just have to stress to users that they must enter valid values for both parameters or expect an error.

Many thanks for your help anyway.

Regards,

Bob

[Ratslinger]

Please see edited answer and review tables for acceptable data to be used in query. It all works. Even used your table and field names so it should be easily transferable.

Done with Ubuntu 18.04 with LO TDF v6.3.4.2

[bbater]

Ratslinger, no inconvenience at all! The maximum string length for ‘AsylumStatus’ is 13, so I put 13 '*'s in the query and it works for all cases now.

Thanks so much!

Regards,

Bob