Wonderware InSQL query - data quality

[Erik vdH]

Does anyone have much experience writing WW InSQL queries.

I have done a bit of it.

There are two different ways to get data from it (it is an OLE DB provider to SQL Server).

1. Narrow Format using a query such as ...

SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = DateAdd(mi,-5,GetDate())
SET @EndDate = GetDate()
SET NOCOUNT OFF
SELECT TagName, DateTime = convert(nvarchar, DateTime, 21), Value, vValue, Quality, QualityDetail = History.QualityDetail, QualityString, wwEdgeDetection
FROM History
LEFT JOIN QualityMap ON QualityMap.QualityDetail = History.QualityDetail
WHERE TagName IN ('IPS_Fwd_Energy.PV', 'IPS_Fwd_VAr_Total.PV')
AND Quality = 0
AND wwRetrievalMode = 'Cyclic'
AND wwCycleCount = 100
AND wwVersion = 'Latest'
AND wwEdgeDetection = 'NONE'
AND DateTime >= @StartDate
AND DateTime <= @EndDate

2) Or Wide Format such as ...

SET QUOTED_IDENTIFIER OFF
SELECT * FROM OPENQUERY(INSQL, "SELECT DateTime = convert(nvarchar, DateTime, 21), [IPS_Fwd_Energy.PV], [IPS_Fwd_VAr_Total.PV], wwEdgeDetection
FROM WideHistory
WHERE wwRetrievalMode = 'Cyclic'
AND wwCycleCount = 100
AND wwVersion = 'Latest'
AND DateTime >= DateAdd(mi,-5,GetDate())
AND DateTime <= GetDate()")

The wide format is very convenient in a lot of situation since you get one date-time stamp and adding more data gives you a wider table.

The one problem I am having with the wide format is that you don't seem to be able to filter out bad quality data using the wide format. I asked the WW guys and they were no help.

Has anyone else encountered this or got another way to get out a wide format of only GOOD quality data?

 

[chi2jjk]

Idea for data retrieval

Perhaps use the results of the 'narrow' query, and transform into the 'wide' format(?)

 

[baref00t25]

It has been a number of years since I've done this, but as I recall the wide format was nice to use, but it had limitations because of the 128 character string issues with Wonderware. I ended up relabeling all my columns to one or two character names.

 

[SteveEv]

You can get around the 128 character limitation by swapping the double quotes for single quotes and vice versa.

ie:

SET QUOTED_IDENTIFIER OFF
SELECT * FROM OPENQUERY(INSQL, 'SELECT DateTime = convert(nvarchar, DateTime, 21), [IPS_Fwd_Energy.PV], [IPS_Fwd_VAr_Total.PV], wwEdgeDetection
FROM WideHistory
WHERE wwRetrievalMode = "Cyclic"
AND wwCycleCount = 100
AND wwVersion = "Latest"
AND DateTime >= DateAdd(mi,-5,GetDate())
AND DateTime <= GetDate()')

What I'm now struggling with is trimming the DatTime value to remove seconds:

ie in above SQL:
SELECT DateTime = left(convert(nvarchar, DateTime, 21),16)

Which then gives "incorrect syntax near "("." for no apparent reason