jeff_denz

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

 

FROM

 

  (SELECT FindEq('UM2555FST',step.starttime,step.endtime,2) as StepStart

 

  FROM

 

    (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

 

    FROM

 

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

Outcomes