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

**URL:** <https://forum.inductiveautomation.com/t/ignition-sql-query-changing-from-vantage-pt-to-pi-server/19205>\
**Category:** Ignition\
**Created:** [August 10, 2018, 8:16pm UTC](https://forum.inductiveautomation.com/t/ignition-sql-query-changing-from-vantage-pt-to-pi-server/19205 "2018-08-10T20:16:36Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Faisal.Shibli](https://avatars.discourse-cdn.com/v4/letter/f/90db22/32.png) [@Faisal.Shibli](https://forum.inductiveautomation.com/u/Faisal.Shibli)\
**Post date:** [August 10, 2018, 8:16pm UTC](https://forum.inductiveautomation.com/t/ignition-sql-query-changing-from-vantage-pt-to-pi-server/19205/1 "2018-08-10T20:16:36Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![R.Alamsha](https://avatars.discourse-cdn.com/v4/letter/r/e5b9ba/32.png) [@R.Alamsha](https://forum.inductiveautomation.com/u/R.Alamsha)\
**Post date:** [August 11, 2018, 2:09am UTC](https://forum.inductiveautomation.com/t/ignition-sql-query-changing-from-vantage-pt-to-pi-server/19205/2 "2018-08-11T02:09:52Z")

</div>

> [@Faisal.Shibli](#):
>
> revising some of the sql qery code in ignition to point from factory talk historian tags from Vantage-Point to OLEDB (osipi server).

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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [August 11, 2018, 1:21pm UTC](https://forum.inductiveautomation.com/t/ignition-sql-query-changing-from-vantage-pt-to-pi-server/19205/3 "2018-08-11T13:21:41Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Faisal.Shibli](https://avatars.discourse-cdn.com/v4/letter/f/90db22/32.png) [@Faisal.Shibli](https://forum.inductiveautomation.com/u/Faisal.Shibli)\
**Post date:** [August 13, 2018, 12:22pm UTC](https://forum.inductiveautomation.com/t/ignition-sql-query-changing-from-vantage-pt-to-pi-server/19205/4 "2018-08-13T12:22:10Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Faisal.Shibli](https://avatars.discourse-cdn.com/v4/letter/f/90db22/32.png) [@Faisal.Shibli](https://forum.inductiveautomation.com/u/Faisal.Shibli)\
**Post date:** [August 13, 2018, 12:26pm UTC](https://forum.inductiveautomation.com/t/ignition-sql-query-changing-from-vantage-pt-to-pi-server/19205/5 "2018-08-13T12:26:25Z")

</div>

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