• Sign In
  1. Messages

Parameterized Query

LabKey Support Forum (Inactive)

View Message

view list print
Parameterized Query WayneH  2017-10-23 21:56
Status: Closed
 
Hi,
I've been testing use of the parameterized query and found that for the fields specified in the query any rows with null values are omitted from the result. Is this normal behavior? How can I have it include null values for those fields where parameters are used? Note, I have included a "case" statement in the SQL to default to unfiltered for the given field if there is no parameter specified

for example:
WHERE Gender = (Case WHEN Sex != '' THEN Sex else Gender END)

Should I do something differently to ensure that the rows where gender is null are not dropped? It otherwise retains rows that contain some data...

Thanks,

Wayne
 
 
WayneH responded:  2017-10-24 12:32
For the time being I just converted 'null' values to "N/A" so there is always a value in the row for the given field that works around the issue but if there was another way to approach this please let me know.

Thanks

Wayne
 
Jon (LabKey DevOps) responded:  2017-10-25 22:34
Hi Wayne,

I'd like some clarification to your question. So are you not entering in anything into a field when making a parameterized query and not getting any results?

Is it possible for you to provide a more in-depth sample for us to look at, like a folder archive that contains a demo list/dataset and parameterized query that is displaying the behavior you're speaking about with the nulls?

Regards,

Jon
 
WayneH responded:  2017-10-30 12:43
Hi Jon,

created a small example for this question that reflects what I have in the project. (Queries are on the query tab)

1. I start with a dataset... which has a couple of fields which are datetime fields.. i chose to recode those into '1' meaning data is present or NULL/"N/A" (recode or recodeNA datasets) - this is only done because of the data I am receiving..

2. I then created another query where the parameters are specified. (date parameters to select a range and one additional for the CIDR field where data may either be 1/Null or 1/NA depending on the dataset)

3. In the query with null values, requesting the same data range and not specifying CIDR I get 3 rows returned , whereas in the query where these null values have even converted to "N/A" I get 8 rows returned (includes the converted rows)
Note: I had to create an exception so that a null value would default to the original otherwise the query would return no results. Seems the parameter cannot pass an empty value through to the query. But once I do this, values that were null have their rows simply dropped..

I attached the example in the archived study. I figure there must be a better way to do this... to allow a query to accept empty values or work with nulls without having to recode those to some value like "N/A"

Thanks,

W
 
Jon (LabKey DevOps) responded:  2017-11-04 21:24
Hi Wayne,

After testing this, I don't think we've properly accommodated an IS NULL option when being used via parameters.

I'll need to escalate this further to see if:

1. There is a better workaround
2. To see how we would support this with the existing structure.

Thank you for your patience.

Regards,

Jon