SimulationAids: View expression function usage

Ignition
1

@pturmel
I’m trying to use your View expression function in 7.9.12, but not entirely sure how to form my simple select query.

I’m trying this:

view("SELECT tagName, tagPath WHERE tagName LIKE '%" + lower({Root Container.TagBrowser.filter.text}) + "%'", {Root Container.TagBrowser.Power Table.raw_dataset})

but I’m getting the error:

Caused by: org.python.core.PySyntaxError: SyntaxError: ("mismatched input 'LIKE' expecting COLON", ('<expression:view>', 4, 13, "  if tagName LIKE '%loca%':\n"))

What am I doing wrong? Or am I perhap reading too literally into the SQL structure of the selectString argument, and the LIKE keyword isn’t supported (converted into Python)?

Also, can I suggest adding in some examples into your doco to show exactly how these work :slight_smile:

2

FYI: I’m using a regex to do this in a python script instead as I needed to get it running.

This is what I’m using to mimic the SQL LIKE operand:

def findLike(searchString, searchTerm):
	'''
	Description:
		Searches the search string for the search term, similar in function to the SQL 'LIKE' operator.
		Accepts wildcards % (any characters) and _ (any single character).
		If searchTerm does not contain surrounding wildcards, the start and end characters in both the searchString and searchTerm must match to produce a match.
	'''
	import re #regex
	# create the regex match string. Need the start and end characters added "^" and "$" as these anchor the match to the start and end of the string.
	# Without these, matches would be found in the middle of the search string surrounded by other characters even if there is no % or _ character at either end.
	# Also, replace SQL % and _ with equivalent regex search terms, .* and .
		
	re_match_string = "^" + searchTerm.replace("%",".*").replace("_",".") + "$"
	# if we replaced wildcard characters that were marked to be escaped (e.g. using [] brackets, then replace these back.
	re_match_string = re_match_string.replace("[.*]", "[%]").replace("[.]", "[_]")
	search_re = re.compile(re_match_string)
	
	return 1 if not search_re.match(searchString) is None else 0

print findLike('hello world!', 'hello%')

The smarts are courtesy of: https://stackoverflow.com/questions/26148712/compare-strings-in-python-like-the-sql-like-with-and

Edit: made into a function instead and handled escaped % and _ wildcards

3

Just as a side note (if you want to make this more universal), you may want to re.escape your string before using it as a regex (in order to avoid issues with special characters like periods or quotes).

4

Thanks Sander, I figured there would be other cases that I haven’t accounted for yet, but was going to cross that bridge when I needed to use it more diversely. Cheers for the info

5

Hi Nick,

You are correct: LIKE is not supported, as that is a SQL expression operator, not python. The PseudoSQL is SQL-ish for structure, but needs python expressions. Also, you should avoid assembling your first argument (the SQL) dynamically. Pass the relevant information as extra args and arrange it appropriately within the python expression. Something like this:

view("SELECT tagName, tagPath WHERE  args[0].lower() in tagName",
  {Root Container.TagBrowser.Power Table.raw_dataset},
  {Root Container.TagBrowser.filter.text})

The best examples are in the sample project.

6

Hi ptunnel,
How to use

view('select Line,WEEK,100*um(unitGP)/sum(unit) as Yield where unitGP is not Null  group by Line,WEEK,Year,unit,UnitGP order by WEEK',{value})

I can not use Null in view function

7

These are pyExpressions. Use is not None as the condition.

1 Like
8

after fixing has been

view ('select Line,WEEK,sum(unitGP),100*sum(unitGP)/sum(unit) Where unitGP is not None group by Line,WEEK,Year',{value})
9

Tip: You can use newline in Expressions to make it much more readable. Also, adding spaces after punctuation (commas) and before and after operators (+, -, *, /) is a big help as it follows normal rules of writing.

view('SELECT 
    Line,
    WEEK,
    SUM(unitGP),
    100 * SUM(unitGP) / SUM(unit) 
  WHERE unitGP is not None 
  GROUP BY Line, WEEK, Year',
  {value}
)
2 Likes