View() expression function where clause not working

I’ve been starting to use pturmel’s view() function to filter data from a tag dataset. In short I have one broad query that supplies data for numerous table objects in perspective. I’m trying to avoid having each table object execute a slightly different query to the database.

The select works fine but I am getting zero rows every time I put in a where clause. I started with this expression:
view(“Select Plant Where Plant ==‘S’”,{[default]query_data/master_data} )
Plant has three values; P, S, and T. Although there are numerous instances of ‘S’, I get zero rows returned.

If I modify the expression to ‘not equal’:
view(“Select Plant Where Plant !=‘S’”,{[default]query_data/master_data} )
I get all rows returned (P, S, and T values), so it is definitely and issue with the equality expression.

I guessed there may be a data type issue so I changed the expression to:
view(“Select Plant, type(Plant), type(‘S’)”,{[default]query_data/master_data} )
This reveals that Plant is of type PyUnicode and ‘S’ is of type PyString.

So I validate I can use the str() function to convert Plant to PyString and try this:
view(“Select Plant Where str(Plant)== ‘S’”,{[default]query_data/master_data} )…no dice.

Finally I tried storing the value ‘S’ in a memory tag (tried both text and string data types) and used it as an argument.
view(“Select Plant Where Plant == args[0]”,{[default]query_data/master_data}, ({[default]query_data/character_S.value}) )
same result.

I am out of ideas…any thoughts on what I could be doing wrong???

Is there any chance your raw dataset has trailing spaces on Plant ?

1 Like

I’ve run into trailing spaces in IBM db2 databases (which may be an AS400/RPG problem). I have to use trim a lot.

trim(field)

I’m so mad I didn’t think of that…
view(“Select * Where Plant.strip() == ‘S’”,{[default]query_data/master_data}, ({[default]query_data/character_S.value}) )
nailed it.

Thanks!!!

1 Like