Old style JOIN (ANSI JOINs) syntax is not allowed. Please use standard syntax

Question

A client has asked me to write a rather complex SQL query to be used on Salesforce. When trying to implement the finished query on the Queries page I get the following error:

Old style JOIN (ANSI JOINs) syntax is not allowed. Please use standard syntax.

My question is what is this error referring to? The WHERE statements used in the code? The JOIN statements? Here's the code:

select z.*, @row_num := if(@prev_value=chk,@row_num+1,1) as journey
from(
select
   f.*, p.arrtime as prev_arr,
   case
when p.depttime is null then 1
when ifnull(hour(timediff(f.depttime, p.arrtime)), 0) <= 4 then
   1
else
   0
end as chk
from
   flights f
left join flights p on f.ref = p.ref
and p.depttime = (select max(z.depttime) from flights z where z.ref = p.ref and z.depttime < f.depttime)
) z,
 (select @row_num := 1) x,
      (select @prev_value := '') y
order by
   z.ref,
   z.depttime;

for the following data:

+------+----------+--------------------+--------+-------------------+
|Ref   |Dept      |DeptTime            |Arr     |ArrTime            |
+------+----------+--------------------+--------+-------------------+
|1     |New York  |2015-02-01 08:00:00 |Boston  |2015-02-01 09:00:00|
|1     |Boston    |2015-02-01 10:00:00 |Chicago |2015-02-01 11:00:00|
|1     |Chicago   |2015-02-01 12:00:00 |Dallas  |2015-02-01 13:00:00|
|1     |Dallas    |2015-02-02 11:00:00 |Seattle |2015-02-02 13:00:00|
|2     |London    |2015-02-01 04:00:00 |Berlin  |2015-02-01 16:00:00|
|2     |Berlin    |2015-02-02 18:00:00 |Moscow  |2015-02-02 23:00:00|
+------+----------+--------------------+--------+-------------------+

Please let me know if you have any questions. Any help will be greatly appreciated!

Answer 1

Adam is correct, you cannot use variables in SQL queries in SFMC.

However, the error message refers to the fact that you do not have a JOIN operation specified in your query.

Simplified your query is structured like this:

SELECT * <br/>
FROM () z, () x, () y

This is what is referred to as an old style join.

You can make use of the CROSS APPLY or CROSS JOIN operations instead:

SELECT * <br/>
FROM () z <br/>
CROSS APPLY () x <br/>
CROSS APPLY () y

Answer 2

Per the Query Activity documentation (SQL Support section), variables are not allowed. You'll need to re-write it without @prev_value and @row_num.

You might want to look at the T-SQL ranking functions. I tend to use those heavily in complex queries.

Sometimes two-pass queries are good workarounds.