View() Function Multiple WHERE conditions

Ignition
,
1

I have successfully loaded the Simulation Aids and use the view() function to query a dataset and return values successfully. However, I would like to use a second filter in the WHERE clause

something Like: ... WHERE Location=='WHSE' AND UNIT=='U2' ...sort of thing.

IS this possible or am I limited to only one WHERE condition?

2

You can use as many as you want. Be sure to use args to reference any dynamic comparison values. Note that the entire expression after WHERE must be valid python, so use a lower case and.

Also, for anything other than pivot operations (which are only implemented in view()), the newer iteration functions are much more performant.

3

Thank you! Turns out the case was my only issue!

4

pturmel -- I really appreciate the view() development tool you created.

I am new to the python environment--I have to admit that the use of the tool and the tuples was a little confusing to me however once I got my mind wrapped around it --it all clicked.

For anyone seeking clarification, the following 2 pieces of code yield equivalent results:

view("select NOMINAL_RATE where LOCATION ==args[0] and ID==args[1]"
	,{[~]DATA/EXT/Rates},{[~]Ext/Unit1/Parameters.UnitID},{[~]Ext/Unit1/ID_Actual})


view("select NOMINAL_RATE where LOCATION =='"+{[~]Ext/Unit1/Parameters.UnitID}}+"' 
	and ID=='"+{[~]Ext/Unit1/ID_Actual}}+"'" 
	,{[~]DATA/EXT/Rates})

Note: The syntax using tuples is cleaner. The second example--I find easier (more intuitive) to read--but does have added punctuation to contend with --more prone to errors.

5

Actually, they don't behave identically. The first version will compile its PseudoSQL once and will thereafter run very quickly. It also will work reliably regardless what your argument data types are.

The second version must compile on every execution, and will break if you try to use numeric columns, or if a value is passed that has a quote character in it.

6

Thank you for the clarification! Those are very significant distinctions...details matter!

Tuples...all the way!

7

Now that I have switched from the view() expression to the new iteration functions, I find it hard to read view() expressions, especially when they get more complex.

Phil can correct me if I am wrong, but I believe that either of the following two expressions would fundamentally return the same results but are more performat, and to my eye easier to read (but that's maybe just me).

Return in Dataset format:

columnRearrange(
    where({[~]DATA/EXT/Rates},
        it()['LOCATION'] = {[~]Ext/Unit1/Parameters.UnitID},
        it()['ID'] = {[~]Ext/Unit1/ID_Actual}),
    'NOMINAL_RATE'
)

Return as a List (Array in Perspective Terms):

transform(
    where({[~]DATA/EXT/Rates},
        it()['LOCATION'] = {[~]Ext/Unit1/Parameters.UnitID},
        it()['ID'] = {[~]Ext/Unit1/ID_Actual}),
    forEach(value(), it()['NOMINAL_RATE'])
)
1 Like
8

Just ditch the double-equals in expressions.

1 Like
9

oops. Great catch on my copy paste, no read.