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

**URL:** <https://forum.inductiveautomation.com/t/view-with-an-or-clause-in-type-of-clause/60216>\
**Category:** 3rd Party Modules\
**Tags:** ignition81\
**Created:** [May 31, 2022, 3:35pm UTC](https://forum.inductiveautomation.com/t/view-with-an-or-clause-in-type-of-clause/60216 "2022-05-31T15:35:47Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 31, 2022, 3:35pm UTC](https://forum.inductiveautomation.com/t/view-with-an-or-clause-in-type-of-clause/60216/1 "2022-05-31T15:35:47Z")

</div>

Tagging creator @pturmel

I have a dataset that looks like this  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/7/b7fc24746350f2b0dcb5aed66f5d3169547e5ebd.png)

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?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [May 31, 2022, 3:39pm UTC](https://forum.inductiveautomation.com/t/view-with-an-or-clause-in-type-of-clause/60216/2 "2022-05-31T15:39:51Z")

</div>

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

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

```

And:

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

```

Bog-standard python `in` operator. 😁

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

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 31, 2022, 3:41pm UTC](https://forum.inductiveautomation.com/t/view-with-an-or-clause-in-type-of-clause/60216/3 "2022-05-31T15:41:36Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 31, 2022, 3:50pm UTC](https://forum.inductiveautomation.com/t/view-with-an-or-clause-in-type-of-clause/60216/4 "2022-05-31T15:50:21Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [May 31, 2022, 4:01pm UTC](https://forum.inductiveautomation.com/t/view-with-an-or-clause-in-type-of-clause/60216/5 "2022-05-31T16:01:38Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 31, 2022, 5:18pm UTC](https://forum.inductiveautomation.com/t/view-with-an-or-clause-in-type-of-clause/60216/6 "2022-05-31T17:18:53Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 14, 2023, 5:50pm UTC](https://forum.inductiveautomation.com/t/view-with-an-or-clause-in-type-of-clause/60216/7 "2023-06-14T17:50:00Z")

</div>

> [@pturmel](#):
>
> It could also implement some syntax closer to true SQL

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

> [@Automation Professionals' Integration Toolkit Module](https://forum.inductiveautomation.com/t/automation-professionals-simulation-aids-v2/74934):
>
> Automation Professionals is pleased to announce the immediately availability of a major upgrade to its free Simulation Aids Integration Toolkit module. The new feature justifying the "major upgrade" language is the implementation of eleven thirty-one new expression functions which use iteration to accomplish complex transforms of datasets and lists, to or from other datasets and lists, applying filtering, ordering, and computed new columns along the way. While there are certain operations that…

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

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [June 14, 2023, 6:11pm UTC](https://forum.inductiveautomation.com/t/view-with-an-or-clause-in-type-of-clause/60216/8 "2023-06-14T18:11:57Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![ahardin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahardin/32/66398_2.png) [@ahardin](https://forum.inductiveautomation.com/u/ahardin)\
**Post date:** [August 14, 2024, 1:24pm UTC](https://forum.inductiveautomation.com/t/view-with-an-or-clause-in-type-of-clause/60216/9 "2024-08-14T13:24:20Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [August 14, 2024, 1:49pm UTC](https://forum.inductiveautomation.com/t/view-with-an-or-clause-in-type-of-clause/60216/10 "2024-08-14T13:49:28Z")

</div>

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

```python
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.

---

<div class="post-metadata">

**Author:** ![ahardin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahardin/32/66398_2.png) [@ahardin](https://forum.inductiveautomation.com/u/ahardin)\
**Post date:** [August 14, 2024, 3:37pm UTC](https://forum.inductiveautomation.com/t/view-with-an-or-clause-in-type-of-clause/60216/11 "2024-08-14T15:37:18Z")

</div>

Thank you very much!! That worked exactly!