SQLExec ODBC Column:table_x.Pwr_T not found

[erblock]

Trying to use SQLExec in Lookout 6.5 to query running default database for MATH_AVG transform, but I get "column:"" not found errors.  No matter what data table value I try to retrieve, it doesn't work.  Hypertrends of same data work just fine.  Have tried substituting '.' with '@' also, but no luck.

What is the deal here??

Ed

[Ryan.S]

What if you just query the table_x.Pwr_T without the MATH_AVG?

Here are some examples, in case you don't write the correct SQL

http://digital.ni.com/public.nsf/allkb/C7D32F9A59D4637086256A7200692F30?OpenDocument

[erblock]

Okay, I tried just data such as table_x.data_y and modbus_x.data_y

Same error:  "Column: modbus_x.data_y not found"

I'm new to sqlexec but I've read a lot and tried many documented syntax's all with the same error.  ??

All of these DataTable and Modbus data members are logged to the Citadel in their Data Member Configuration setup.

Also, SQLExec object is in same process and folder as data member objects and the DSN is pointing to the live, Citadel running for same process (and others).

Lookout 6.5, 32-Bit, Win7

[Ryan.S]

Can you show me the complete SQL statement you use in the SQLExec object?

Do you have Excel on your computer? Another way to check the data and SQL is the Excel SQL query tool.

http://zone.ni.com/devzone/cda/tut/p/id/2970

If you don't have Excel, let me find a SQL utility for you. The SQLExec object in Lookout is just a SQL query tool. It calls the Citadel ODBC driver directly and execute the SQL statement.

[erblock]

Ryan, here is the statement I'm testing tonight....

"SELECT LocalTime, ""ntd001a/crs/tb1_cr.Pwr_TT"", MATH_AVG(""ntd001a/crs/tb1_cr.Pwr_TT"") FROM IntData WHERE IntInterval = ' 00:01:00' AND LocalTime > ' 2011-03-24 02:00:00' AND LocalTime <= ' 2011-03-24 03:00:00'"

Thanks,

Ed

[erblock]

This statement works fine in MSQuery:

SELECT IntData.LocalTime, IntData."ntd001a/crs/tb1_cr@Pwr_TT", MATH_AVG(IntData."ntd001a/crs/tb1_cr@Pwr_TT"), IntData.IntInterval FROM NICIT.IntData WHERE ((IntData.LocalTime>{ts '2011-03-24 02:00:00'}) AND (IntData.LocalTime<={ts '2011-03-24 09:00:00'})) AND ((IntData.IntInterval='00:15:00')) ORDER BY IntData.LocalTime

This was run remotely without issue.

I don't understand why Lookout 6.5 SQLExec cannot find the "columns" of data.

Any more ideas or tests?  I really need Lookout to do these queries like once a minute on 20 minutes of data, returning atleast the average over the interval.  Don't really need the times and interval returned, just the average.

Also, is the MATH_AVG using all logged data available within each interval to get this interpolated average?

Thanks,

Ed

[Ryan.S]

It looks like the problem is in Lookout.

Can you upload your database to ftp://ftp.ni.com/incoming? I want to use your SQL to query your database.

If the database is too big, you can archive one day worth of data to a new database and upload the new one. Thank you.

[erblock]

Done...

OneDay.zip

Ed

[Ryan.S · Accepted solution]

You need to use @ instead of .

This is what I set

"DSN=C__OneDay_OneDay_Database;"

"SELECT LocalTime, ""ntd001a/crs/tb1_cr@Pwr_TT"", MATH_AVG(""ntd001a/crs/tb1_cr@Pwr_TT"") FROM IntData WHERE IntInterval = ' 00:01:00' AND LocalTime > ' 2011-03-24 02:00:00' AND LocalTime <= ' 2011-03-24 03:00:00'"

I don't get error on execution.

[Ryan.S]

I also tried the Win7 64bit without alarm.

How about you log a trace without dot, such as the Pot1. Log it's "implicit" datamember. And then try to query it. If it doesn't work either, the problem is either in the ODBC driver, or the DSN you set.