AnsweredAssumed Answered

Why can't I join a SQL table against PIInterp2 when it works with PISnapshot in OLEDB?

Question asked by Asle Frantzen Champion on Jul 2, 2015
Latest reply on Aug 18, 2015 by pthivierge

I'm struggling with this problem:

 

I'm creating a SQL Server table to hold some data for a report. It should contain the tag name, the archive value 1 day ago and then snapshot value. The 1400~ tags are located in another SQL Server table, so naturally I'd like to join that in. Unfortunately, I can't use my usual OPENQUERY way of querying the PI server because it's limited to max 8000 characters. (I tried building the SQL query dynamically as well, same thing there.). So I'll have to use this way of querying.

 

Any ideas why this works with PISnapshot, and not PIInterp2? Take a look at this:

 

This works as expected:

 

SELECT s.tag, s.value
FROM [LINKED_PISERVER].[piarchive]..[pisnapshot] s 
WHERE s.Tag in (SELECT Tag FROM dbo.TagTable)

 

This does not work:

 

SELECT i.tag, i.value
FROM [LINKED_PISERVER].[piarchive]..[piinterp2] i
WHERE i.Tag in (SELECT Tag FROM dbo.TagTable) 
AND i.time = CAST(DATEADD(DAY,-1,GETDATE()) AS VARCHAR(Max))

 

But this works:

SELECT i.tag, i.value
FROM [LINKED_PISERVER].[piarchive]..[piinterp2] i
WHERE i.Tag in 
('18ESVB1001/Y/PRIM','18ESVB1003/Y/PRIM','18ESVB1004/Y/PRIM','18ESVB1011/Y/PRIM','18ESVB1011_02/Bronn_open/PRIM','18ESVB1012/Y/PRIM')
AND i.time = CAST(DATEADD(DAY,-1,GETDATE()) AS VARCHAR(Max))

Outcomes