Ignition SQL query changing from vantage-pt to pi-server

Ignition
1

I am new to Ignition and also to sql. I am at the moment revising some of the sql qery code in ignition to point from factory talk historian tags from Vantage-Point to OLEDB (osipi server). Please find below the old script vs the new script. The new script returns with the following error
- caused by GatewayException: [PIOLEDB] [SQL Parser] [Line 1] Syntax error near 'As'.
- caused by SQLException: [PIOLEDB] [SQL Parser] [Line 1] Syntax error near 'As'.

Your help is highly appreciated.

OLD CODE
WITH Line1 As(
SELECT cast(localTimeStamp as datetime) as [datetime], valueAsFloat as [ABC123]
FROM dbo.History ('','System.Sources.FactoryTalk.ft-directory.Historians.Production Historian.Tags.P025001_ABC123_XICOND','{Root Container.Start Date.formattedDate}','{Root Container.End Date Graph.formattedDate}','FT-VantagePt')
), Line2 As(
SELECT cast(localTimeStamp as datetime) as [datetime], valueAsFloat as [DEF456]
FROM dbo.History ('','System.Sources.FactoryTalk.ft-directory.Historians.Production Historian.Tags.P025001_DEF456_XICOND','{Root Container.Start Date.formattedDate}','{Root Container.End Date Graph.formattedDate}','FT-VantagePt')
)
SELECT Line1.[datetime],
Line1.[ABC123],
NULL as [DEF456]
FROM Line1
UNION ALL
SELECT Line2.[datetime],
NULL as [ABC123],
Line2.[DEF456]
FROM Line2

NEW CODE
WITH Line1 As ( SELECT cast(time as datetime) as [datetime], value as [ABC123]
FROM picomp2 where tag like 'P025001_ABC123_XICOND' and time between {Root Container.Start Date.formattedDate} and {Root Container.End Date Graph.formattedDate}
), Line2 As( SELECT cast(time as datetime) as [datetime], value as [DEF456]
FROM picomp2 where tag like 'P025001_DEF456_XICOND' and time between {Root Container.Start Date.formattedDate} and {Root Container.End Date Graph.formattedDate}
)
SELECT Line1.[datetime],
Line1.[ABC123],
NULL as [DEF456]
FROM Line1
UNION ALL
SELECT Line2.[datetime],
NULL as [ABC123],
Line2.[DEF456]
FROM Line2

2

Which database do you have?. Is it SQL server or MySQL or ....?. Are you able to run this sql code directly with their query tool (Management studio, workbench etc)?. If it works, just copy paste the code into Ignition. It will work.

3

The construct WITH Line1 AS (SELECT ..... is called a Common Table Expression, and is a relatively recent addition to many database products, MySQL v8 for example. A decade or so for the top products. A brief look at OSI PI’s OLEDB bridge documentation gives no indication it is supported.

It looks like the typical use of CTEs, recursion, isn’t applicable here, and the original query could substitute those SELECTs as nested queries right in the UNION. However, I didn’t see nested SELECTs or UNIONs in my brief review of PI OLEDB. I don’t have it to play with, so you’ll have to experiment with PI’s OLEDB Tester to figure what works.

4

I am using SQL database. I was using PI SQL Commander partially to run SQL query. I will try that.

5

Thanks pturmel! I will give it another go today and see if I can get the code to work.