mikeaird

RDBMS Interface Allowed SQL

Discussion created by mikeaird on Sep 18, 2014
Latest reply on Sep 26, 2015 by bala

I have set up an Interface to retrieve data from an SQL server and using Input Points via Tag Distribution backfilling using Input Recovery to write to PI. 

 

I am collecting from 2 tables and would like to retrieve the points in 1 SQL call using UNION ALL.  The manual doesn't really say what you cannot do with SQL other than saying SELECT, UPDATE, INSERT, DELETE are the only valid SQL statements. 

 

If i split my SQL into 2 files and use 2 Distributor Tags to carry the SQL it works fine but that seems a little cumbersome when i could retrieve the data in 1 SQL call.

 

When i try and UNION ALL, the interface complains and i get the ODBC error 07009 Invalid Descriptor Index error. 

 

Can the interface handle a UNION ALL, or am i doing something wrong in my statement?

 

select Sample_Date as PI_TIMESTAMP, REPLACE(CAST(UPPER('TESTGL_'+Equipment_Name+'_'+Variables) as VARCHAR(80)), ' ', '_') as PI_TAGNAME, CAST(UPPER(OutputValues_PI) as VARCHAR(80)) as PI_VALUE

from PetexDP.dbo.v_custom_apache_VanGoghGL_BK_ALLOC_Results_For_PI

where OutputValues_PI is not null

and [Input / Output] = 'Output'

and row_read = 0

and Sample_Date > ?

and Sample_Date < dateadd(minute, +10, getdate()) /* PI won't accept anything more than 10 minutes in the future */

UNION ALL

select Sample_Date as PI_TIMESTAMP, REPLACE(CAST(UPPER('TESTPROD_'+Equipment_Name+'_'+Variables) as VARCHAR(80)), ' ', '_') as PI_TAGNAME, CAST(UPPER(OutputValues_PI) as VARCHAR(80)) as PI_VALUE

from PetexDP.dbo.v_custom_apache_PROD_BK_ALLOC_Results_For_PI

where OutputValues_PI is not null

and [Input / Output] = 'Output'

and row_read = 0

and Sample_Date > ?

and Sample_Date < dateadd(minute, +10, getdate())

order by PI_TIMESTAMP ASC;

P1=TS

 

Outcomes