release 22.09.01 – dblookup WHERE clause value is empty, returns results

In our current version of r19.1.2.1, at least in a translate Table action (but possibly in a TCL proc), when our SQL SELECT query has a WHERE clause and the <variable> value is empty (”), we get back nothing. For instance, in a translate Table action, and the output of the Table action is put into an HL7 field, what is put there is empty. I don’t want to say it is NULL because that means different things in different context. But if you look at, say an ADT^A08 message, and we are populating PV1.8 depending on a table action, and the input to that Table action is empty (”), there is no value in the PV1.8.

example: PV1||I|||||345678||987654|||| etc – there is nothing in PV1.8

In release 22.09.01, if the SQL table being queried actually had empty values in the WHERE clause column, across multiple rows, what you get back from that SQL call is a list of all the values in the SELECT clause.

example: SELECT provider_id_facility1 FROM provider_table WHERE primary_provider_id = <primary_provider_id>;

And <primary_provider_id> is empty (”). If there are eleven rows in the table where primary_provider_id is empty (”), then you get eleven results back, with the eleven values  found in column provider_id_facility1. And the values are separated by up carrots.

example: PV1||I|||||345678|^^^^112233^^556677^^^^343434|987654||||

So I believe this is different behavior between the two releases. Also there is some strange behavior where if there are multiple rows in the table, and some have values in the selected column and some do not, you only get the multiple rows back, up to the point where there is a selected column with a value in it.

Example – if there are twenty rows in the table with ” in column A, but the “last” row (in the select ORDER BY) that has a value in column B is the eleventh row, you only get eleven rows back. Th other nine rows’ selected column is not returned.

Has anyone seen this? Perhaps the behavior in r19 is a bug and it is fixed in a later release, so now instead of skipping the query if the input variable is empty, it actually runs the query, using the empty input value.