The following query results in a column with times and some NULL values (as I would expect).
(SELECT FindEq('UM2555FST',step.starttime,step.endtime,2) as StepStart
(SELECT phase.time as starttime, FindNE(phase.tag, phase.time + '1s','12/5/2012 12:01:00 AM',1) as endtime, value, PrevVal(phase.tag, phase.time) as previous
(SELECT phase.time, phase.tag, phase.value
FROM piarchive.picomp2 phase
WHERE tag = 'UM2555FPH' and time between '11/3/2012 12:01:00 AM' and '12/5/2012 12:01:00 AM' and value = 1
ORDER BY time) as phase
WHERE PrevVal(phase.tag, phase.time) not in (1,'Bad Input')) as step) as c1
I am not having any luck filtering out the NULL (at least is appears NULL although the PI Application User Manual indicates it would be a bad value returned) values that are returned by the FindEq function. The bad value is being returned because FindEq does not always return a result, as expected.
I need to be able to filter our the NULLS or bad values. I have attempted using the badval function to determine if it's bad and filter out those but the BadVal function fails with the error Function Name BadVal is not valid.
I have also tried the IS NOT NULL comparison and this fails with an overload resolution error.
What I have noticed is that I am able to sort the results and even group them but I get an error when attempting to filter our the nulls in any way.
How do I filter out the bad or null values using PI OLEDB?