Filter out the "NULL" and\or bad values returned by the query using a PI function PI OLEDB

Discussion created by jeff_denz on Apr 3, 2013
Latest reply on Apr 12, 2013 by hanyong

The following query results in a column with times and some NULL values (as I would expect).




SELECT c1.StepStart




  (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?