[Solved] Conditional Query. Filter with a ListBox & Sel ALL

[kokoliso]

Hello.
I am trying to make a Filter in a Form with a ListBox of "Workers" and I get success filtering when selecting a worker and refreshing a form. But I would like to show all the workers when selecting something like a "All workers" item.
In the Listbox I can see the list of all the workers and the first value is something like a "blank" or NULL or EMPTY ( I am not sure. I can set this in the cell where it is stored the data).
Then, I was thinking that when this "NULL" value is stored in the filter cell, then would be great that when I run a query that doesn't filter by worker, something like a condition after the "WHERE" statement.
I have been searching and in HSQL docs says something about CASE or CASE WHEN, but when I try to run a query doesn't recognize CASE. Something like this:

SELECT "Worker_ID" AS "SelectedWorker" FROM "Filters" WHERE "Filter_ID" = '0'
CASE "Worker_ID" IS NOT NULL THEN SELECT * FROM "Assets" WHERE "Worker_ID" = "SelectedWorker";
ELSE SELECT * FROM "Assets";
END CASE

[Villeroy]

[Tutorial] Cascading Listboxes with macros

[Villeroy]

The above link describes how to filter listboxes by other listboxes using a macro.
The special filter in the "Filter Data" form of this demo file filters a subform grid by 2 list boxes and 2 dates. The list boxes simply show nothing for "no filter". If you want a list box with an explicit <All Workers> entry, you can do it with a UNION SELECT in direct SQL mode ("native SQL").
In the example file the following works fine with the Persons list box filter:

Code: Select all

SELECT '<All Workers>' AS "Name", NULL AS "ID" FROM "Persons" LIMIT 1
UNION (SELECT "N", "ID" FROM "Persons" ORDER BY "N")

it shows the sorted list of names with <All Workers> on top writing a Null value or the respective ID into the filter table.

[kokoliso]

Thanks!!!!
I don't understand the code and the JOIN function of SQL, but opening the example you sent I saw that when you select "" (NULL) from the dropdownlist of names, when filtering you can made an OR operation and now it works:

SELECT "PID", "CID", "D", "V", "ID"
FROM "Data"
WHERE ("PID"=:paramP OR :paramP IS NULL)

I have added this OR operation and now when I select the NULL value from the dropdownlist, it filters nothing

THANK YOU VERY MUCH!!!!