View() with an OR clause/IN type of clause?

3rd Party Modules
1

Tagging creator @pturmel

I have a dataset that looks like this
image

that I am trying to split into two different datasets, one is the top three rows - where roleId = 25, 26, 24, and the second dataset is the remainder of the list.

I was looking at the the example project but only found one where clause example like Select t_stamp, Ramp Where _r>(_from.rowCount-100) but it’s not exactly what I need.

I know I could currently do it with a LIMIT clause but a new role is something we could realistically add and I don’t doing that to mess things up so I’d rather select exacltly the rows I need.

How can I select exactly the rows I need and then how to select the remainder rows?

2

The view() function’s WHERE clause is a python expression. The two expressions you need are:

view("Select * Where roleId in (24, 25, 26)", {Path.to.source.data})

And:

view("Select * Where roleId not in (24, 25, 26)", {Path.to.source.data})

Bog-standard python in operator. :grin:

{ Looks a bit like SQL but isn’t. }

1 Like
3

Ah I see - my issue was I had in uppercased to IN like I was typing SQL. Putting it to lower case worked for me. Thanks!

Ah ok, you said its a py expression I do see/get why it should be lower case. Makes perfect sense now.

4

Only semi-related but I don’t suppose there’s more plans to expand Simulation Aids?

What would be great imo is a union keyword that combines two datasets (assuming same number of column/column types). I have situations where on the the same window I will have a dropdown list that needs to be selected by a user so the first option will be None and then the list of options, and on the same window a dropdown Filter based on those same options which needs an All as the first option before the actual options. Right now I do that with a named query with a string parameter that populates the first row with None or All or whatever is needed, but then the named query still needs to be called twice to populate both dropdowns.

Just wanted to throw that idea out there.

5

Oh, there are always ideas percolating. Union is ugly. I wouldn’t make it part of view(), but a separate function. Join is the one I keep thinking about, but it is even more ugly.

What is most likely to happen first is an alternate version of view() that leverages Ignition’s expression machinery to execute off of an object tree instead of jython. I expect such a beast to be orders of magnitude faster than the current function. It could also implement some syntax closer to true SQL, like auto-grouping when aggregates are present in the Select list, or an efficient implementation of mixed ascending and descending ordering.

Percolating.

2 Likes
6

Yea I was thinking it would be an expression like union({Root container.datset1}, {Root Container.dataset2}, ...) would be the usage.

I expect such a beast to be orders of magnitude faster than the current function. It could also implement some syntax closer to true SQL, like auto-grouping when aggregates are present in the Select list, or an efficient implementation of mixed ascending and descending ordering.

This all sounds great!

7

This part didn't happen, but the script-less high-performance toolkit to do this stuff in expressions has arrived:

Percolated for a whole year before the right combination popped into my head.

1 Like
8

Ha I was just thinking of this post after you made the SimAids V2 announcement. Glad you got it working, I will be using it!

9

Is there a way to write this so that the value in the Where clause is pulled from the value of a memory tag? Thanks!

10

Sure, the view function accepts an additional args which can be accessed in the select string.

view(
    "Select * Where roleId in (args[0],args[1],args[2])",
    {Path.to.source.data},
    {Path.to.value_1},
    {Path.to.value_2},
    {Path.to.value_3}
)

Of course depending on what you are actually doing, the new functions added to the module may provide a better way to accomplish you goal.

3 Likes
11

Thank you very much!! That worked exactly!