Using picomp2 table to query two tags to return in their individual columns

Ignition
1

Hi,

Using the Pi Interpolation table I am able to create a table with individual colums with their corresponding tag values. I want to create similar table in another words table with two individual tag value colums but using ‘picomp’ table. I used Common Table Expression methodology to create the first table and below is the code. I have also included the code that I am using with picomp but it doesn’t seem to work. I am using ignition so you may find the syntax a tad bit different. Your help is greatly appreciated.

Table created using piinterp

piinterp code snip

SELECT CAST(Line1.time as datetime), Line1.value as FIT12513, Line2.value as AIT12515 FROM piinterp2 Line1 INNER JOIN piinterp2 Line2 ON Line2.time=Line1.time where Line1.tag = ‘XX10001_FIT12513_VAL’ and Line2.tag = ‘XX10001_AIT12515_VAL’ and Line1.time BETWEEN ‘{Start Date}’ and ‘{End Date}’ and line1.timestep = ‘1h’

picomp2 snip
SELECT Line1.[datetime], Line1.[FIT12513], NULL as [AIT12515] FROM ( SELECT cast(time as datetime) as [datetime], value as [FIT12513]
FROM picomp2 where tag like ‘XX10001_FIT12513_VAL’ and time between ‘{Start Date}’ and ‘{End Date}’) Line1
UNION
SELECT Line2.[datetime], Line2.[AIT12515], NULL as [FIT12513] FROM ( SELECT cast(time as datetime) as [datetime], value as [AIT12515]
FROM picomp2 where tag like ‘XX10001_AIT12515_VAL’ and time between ‘{Start Date}’ and ‘{End Date}’) Line2

2

I don’t know what the Pi Interpolation table is, but here are a few suggestions:

  1. Try breaking you query down and testing each part of it.
  2. You are using the LIKE operator, but there are no wildcards in the pattern! Either use wildcards, or just use ‘equals’.
  3. In the first example you use the tag ‘XX10001_AIT12515_VAL’, but in the second example you use the tag ‘XX10001AIT12515_VAL’. Are these meant to be the same?
3

Pi Interpolation table is piinterp table from which is associated to PI OLEDB database. I will try ‘=’ and also use wild card and see if it yields a different result.

Yes David, the two tags are the same. I just changed my tags for privacy purposes so it was a typo. These tag names are different and I can confirm the tag names are correct.

Thanks again for your suggestion.