SQLExec ETM funtion error in returned value

[Ian (Optimumfx)]

I have discovered a potential bug within Lookout 6.1 which is causing problems within our application and am hoping that someone can help

I have a SQL query which is using the ETM function on a Boolean variable and when this is executed it sometimes returns an incorrect value. I believe that providing the variable being queried has changed state over the given time period the result returned is correct, however if the variable has been true for the whole time period then the result is incorrect i.e. it returns zero.

Any help would be apprectiated.

[Mike@DTSI]

Can you post the SQL statement and a quick example.  I have not run into this issue, and have hundreds of boolean (high/low) values being pulled.

Mike

[Ian (Optimumfx)]

Mike, I have just created a small example process if you are interested in having a look - we are sending this to Ryan at NI to have a look.  We are experiencing some very odd behaviour with the SQL where sometimes it is retieving good data and other times it is not.  How can I get this to you?  Ian 

[Mike@DTSI]

Attach it to a post.  of send mikecrabtree (at) destekinc.com  (change the (at) :))

Things to keep in mind about the sql returns that are funny.  In order for me to get from the beginning of the month (05/01/2008 for example) i have to the start time as 05/02/2008.  This returns the 05/01 value first!  very odd.  Also, all times are returned in seconds.  This totally messed up our Lookout 5 systems, as the that returned the value in percent of day. 

Mike

[Ian (Optimumfx)]

Mike, great - I'll ask my colleague Andy to copy you in on email to Ryan. 

I knew about the etm seconds thing as we do lots of sql stuff and had a big job converting from 5 to 6.  Wasn't aware of the date issue, not sure if I still fully understand. Ian

[Mike@DTSI]

here is an example.  I want to pull the data for a whole month (February).  so 02/01/2008-02/29/2008 (leap year :))

First row the 1st day (correct):

SQL: SELECT MATH_ETM(TO_DISCRETE(""MyMachine/Process/Folder/Value"")) FROM IntData WHERE LocalTime BETWEEN '2008-02-02' AND '2008-02-29' and IntInterval ='1'
Returns the first row as the etm for the 1st!

First row the previous month (wrong):

SQL: SELECT MATH_ETM(TO_DISCRETE(""MyMachine/Process/Folder/Value"")) FROM IntData WHERE LocalTime BETWEEN '2008-02-01' AND '2008-02-29' and IntInterval ='1'
Returns the runtime of the last day of the previous month. 

I played with this for weeks.  Was a lot of fun to make it work on the reports.

Mike

Edit: Dont know why i had february as 4 my bad haha


Message Edited by Mike@DTSI on 05-02-2008 12:45 PM

[Mike@DTSI]

Using the following: 

"SELECT MATH_ETM(""mikedesktop/DataTable_SQL2/Switch1@value"") FROM IntData WHERE IntInterval = '"&Exp_Interval1&"' AND LocalTime > '"&TE_From&"' AND LocalTime <'"&TE_To&"'"

started logging high from 10:57:00.  ,
values from 10:58:00 to 10:59:00 return 60
values from 10:58:00 to 11:00:00 return 120 (120 seconds)
values from 10:57:00 to 11:00:00 return 180

set to low just over 1 second
values from 11:00:00 to 11:05:00 return 298.35 (which is correct for 5 minutes minus my quick on off)

set low at 11:06:10+
values from 11:06:00 to 11:07:00 return 10.87
values from 11:06:30 to 11:07:00 return 0

appears to be correct throughout my tests.

Any thing i am not doing you are?

Mike

[Ryan.S]

I'm not sure what's wrong with it. From my tests, I can reproduce the problem. If the interval is equal to query time, and is set to some specific values, such as 2 minutes, I see the query result = 0, not 120.

Mike, what's your Lookout version? Any DSC stuff installed? I want to check your Citadel version.

[Mike@DTSI]

6.0.2 with latest patches, regular citadel install from cd.   MSDE replaced with SQL server 2008.

no labview.

Mike

[Ian (Optimumfx)]

We are using Lookout 6.1 on windows xp sp2, sql express 2005, plus the patch for 6.1.