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!