AnsweredAssumed Answered

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

Question asked by mehsham on Oct 10, 2018
Latest reply on Oct 17, 2018 by mehsham

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

Outcomes