Writing a Tag query

[Former Member]

Hi all,

I need to write complex select statements on Tag database.

Can anyone please let me know how and where in query template to write.

Thanks

Ravi Kumar.

[jcgood25 · Accepted solution]

What is your Tag source system? Many of the mainstream historians support SQL statements, typically using a specialized syntax, but you will have to create a SQL based connection to the historian. If you are able to setup an SQL based connection, then the FixedQuery will be your place for custom tag requests.

Regards,

Jeremy

[Former Member · Accepted solution]

Hi Jeremy,

That's what I am looking for. I am willing to write a SQL statement on tag database. I tried using UDC connector for creating the connection, but I found it's impossible to write statements there.

I am using PI System. Can you suggest which type of connector do I need to use for accomplishing that.

Regards,

Ravi kumar.

[jamie_cawley · Accepted solution]

You will need to obtain the PI Data Provider, either from the PI install cd or the OSISoft site, and the UDS admin, which is available on the service marketplace.

The UDS admin has instructions for creating the connection.

Regards,

Jamie

[agentry_src · Accepted solution]

In my distant past, I believe we wrote sql queries agains PIBatch. You may want your connector to point there. I also vaguely remember it being an ODBC connector, but it was a couple of years ago.

Mike

[jcgood25 · Accepted solution]

Use the PI OleDB provider, with the OleDB UDS (bundled in the 4.0 installation along with OPC-DA and OPC-HDA).

Regards,

Jeremy

[Former Member · Accepted solution]

Once setting up the UDS connection as above, you'll need to connect your datasource to the exposed port of the UDS using a "OLEDB Connector", port must match that of the SAP UDS.

from there you can simply drop a PI tag query into the query template to extract data.

SELECT "tag", "time", "_index", "value", "status", "questionable", "substituted", "annotated", "annotations" FROM "piarchive".."picomp2" WHERE tag = 'sinusoid' AND time BETWEEN '-1h' AND ''

[Former Member]

Once setting up the UDS connection as above, you'll need to connect your datasource to the exposed port of the UDS using a "OLEDB Connector", port must match that of the SAP UDS.

from there you can simply drop a PI tag query into the query template to extract data.

SELECT "tag", "time", "_index", "value", "status", "questionable", "substituted", "annotated", "annotations" FROM "piarchive".."picomp2" WHERE tag = 'sinusoid' AND time BETWEEN '-1h' AND ''

[Former Member]

Hi Jeremy,Jason

After installing PI OLEDB Provider,OLEDB UDS Connector on xMII Server and doing a set of configurations in Admin Console and Data servers, I am able to connect to PI Data Base and I am able to write SQL as we do on any other Relational Data base.

Thanks for your invaluable information.

Regards,

Ravi kumar.