AnsweredAssumed Answered

SQL Commander with CROSS APPLY

Question asked by narburgh on Dec 13, 2017
Latest reply on Dec 13, 2017 by pmartin

Hello,

I am trying to run this code:

select

t1.tag,

t1.time,

t1.value,

t2.value as critno

from piarchive..piinterp2 as t1,

cross apply

(    select

     t3.tag,

     t3.time,

     t3.time - time('15s') as time_minus,

     t3.time + time('15s') as time_plus,

     t3.value

     from piarchive..picomp2 t3

     where t3.tag = 'RE392CPP.CRITNO'  -- specific tag I am interested in

     and t3.time between '2017-10-14 17:00:21.1660000' and '2017-10-18 21:00:49.6500000' -- time frame for when I want to know when the tag changes

     and t3.value != 0

     ) t2

where t1.tag LIKE 'RE392CPP*'

and t1.time between t2.time_minus and t2.time_plus -- want to pull data from t1 for every row created in t2

and timestep = '1s'

order by t1.time, t1.tag

 

What I want to do is to pull data from t1 for every row that is created in t2 and interpolate between the time_minus and time_plus range. I am not familiar with CROSS APPLY but I believe this command will do what I want. Whenever I run this query, however, I get this error:

[SQL Parser] [Line 7] Syntax error near 'cross'.

 

Can someone please advise as to what I am doing wrong? And if you have any ideas on if there is an easier way to do this?

 

Thank you so much!

Outcomes