Filtering SQL Query for Overview

Ignition
1

Is there a way to filter a SQL query further than the original statement? I have a query of temperature information for the past 8 hours. Within this, there are twenty different smokehouses that I need to separate the temperature information for and display in charts. I am aware of the row and column viewer tools but since there won’t be user interaction with this screen, I imagine there is a better way to filter this information without either having 20 different row viewers for the different smokehouses or running 20 separate SQL queries on a single page.

Any help on how to further filter a single SQL query in a way similar to the row viewer tool without having to actually use the row viewer would be greatly appreciated.

2

You can use expressions in SQL queries to filter results.

Here’s an example:

SELECT Description, LocalPath FROM equipment WHERE type = '{Root Container.equipType}'

3

That is what I’m trying to avoid actually. Because there are twenty different smokehouses, I’d need to use that same query 20 different times switching the where statement for each of them. Running this many queries will bog down my system and the screen.

The row viewer has the ability to group and separate the information by smokehouse. I am asking if there is a way to do this similar to how the row viewer does it but without actually using the row viewer since I don’t want to have twenty of them for each chart I need to create.

I should note that because this query polls for results over the past 8 hours, if a smokehouse hasn’t been in operation during that time, no information for that smokehouse will be returned. This would make using multiple row viewers difficult because the information in them for the house numbers has the potential to change throughout the day. (A house disappears or another house is added to the list, for example)

4

I think nmudge was recommending using something like a dropdown list containing the different smokehouses, then the charts would then query with the where clause using the selected name.

Would that be a viable solution for you?

5

Yes, Stevenson.Yuan more fully described what I was thinking.

6

You can just turn it into a PyDataset and use Python to filter it out to a new dataset(s).
That would let you run the SQL query once.
I do something similar with a bunch of production data I need to fold, spindle, and mutilate quickly from a fragile DB.

7

I’m not sure what your dataset looks like (if you could post the table definition that would help), but you can also use dynamic values anywhere in your SQL query binding. IE:SELECT t_stamp, {Root Container.col1name}, {Root Container.col2name} FROM {Root Container.tablename} WHERE {Root Container.col3name} = '{Root Container.col3value}'I hope this helps,