Data Views

Data Views are an easy way to use custom SQL scripts to view data. They are accessed from the Asset menu under Data View.
To create a new Data View open Tools->Database->tblExternalDataQueries and enter the required columns.

ID – Auto Generated
Description – The name of this view as it wil appear in the menu
ConnectString – Leave blank to connect to the xspoc database, or provide a connection to another database.

Example Connection Strings:
Provider=SQLOLEDB.1;Password=1234;Persist Security Info=True;User ID=SpocWrite;Initial Catalog=xspoc;Data Source=hosting01.xspoc.com,4510

Password=1234;Persist Security Info=True;User ID=SpocWrite;Initial Catalog=xspoc;Data Source=hosting01.xspoc.com,4510

SQLText – Enter your query here. It must be on a single line. There are some keywords to help you write the query.

Keywords:
xsnodeid - the currently selected asset
xsnow - the current date
xsotherwellid1 -  the OtherWellID value for the currently selected asset

Example Queries:
SELECT * FROM tblNodeMaster WHERE PortId=2
SELECT * FROM tblNodeMaster WHERE PocType=8
SELECT * FROM tblCurrRawScanData WHERE nodeid = xsnodeid
SELECT * FROM tblDataHistory WHERE address = 32614 AND date > '2021-03-04' ORDER BY date DESC
SELECT DISTINCT Nodeid, FluidAbovePump, TestDate FROM tblwelltests WHERE FluidAbovePump >= 0 ORDER BY testdate DESC
SELECT PocType,Count(PocType) as count from tblnodemaster group by poctype order by poctype

You do not need to restart services for these changes.