SimulationAids (View expression): Filtering Dataset based off Column Value using String Wild Cards

Ignition
1

Hi,
I am trying to use the view expression (direct binding to component) from SimulationAids to filter a dataset based off a String value that is a wildcard, I am having some issues since I am not sure of the pseudo-SQL syntax.

Just for example (where the column name is Station_Name):
view("SELECT * WHERE Station_Name== '%North%'", {[default]dataset})

This doesn't work, but basically unsure of the syntax to search if the column has that substring ('North') and return the rows in the dataset that have it. the % operators don't work.

Any help is appreciated, I want to see if this would be simple using the module, otherwise I'll just use python and SQL and not the SimulationAids module.

2

I don't know, but == isn't valid SQL and you can't use the single = with wildcards. Try,

SELECT * WHERE Station_Name LIKE '%North%'
3

The problem is that the view() expression doesn't use SQL, it's pseudo-SQL. The like operator doesn't work

4

The code works if the string matches.

For example, this code works for rows where the column name is 'North'.

view("SELECT * WHERE Station_Name== 'North'", {[default]dataset})

But I want it to work and return the rows if the string in the column contains 'North' in the string.

Having issues with the syntax for the wildcard since '%' doesn't work

5

OK. Looking through the manual it says,

Select
  pyExpression,
  pyExpression As ColumnName,
  pyExpression As "Column Name" ...
Where pyConditionalExpression ...

The "pyCondidionalExpression" suggests to me that I'm wrong and that your == is correct but your % wildcard is the problem as that is SQL syntax.

6

Yes, the '%' is the issue. I am not sure how to implement this in the view() expression.

7

This (from the manual) might be useful:

When in doubt how view() is handling your code and data, set its logger to DEBUG, or wrap in the debugMe() expression function. This will show you the python whenever it re-compiles, along with execution times. Looking at the generated python will also show other variables you may use in your expressions.

8

Here comes Phil!

9

You would use jython's in operator to test for a string within another string. Like so:

view(
	"SELECT * WHERE 'North' in Station_Name",
	{[default]dataset}
)

However, consider using the where() expression function instead, like so:

where(
	{[default]dataset},
	it()['Station_Name'] LIKE '%North%'
)

view() launches jython, which has much more overhead than the Integration Toolkit's other iteration functions.

{ Ignition's expression language does have the LIKE operator. }

1 Like
10

Ok, that works. Thanks a lot!