Illegal reference to correlation name

[Former Member]

I'm doing some testing to convert from an ancient version of SQLA to v17 and I'm running into many datawindows that no longer function.

The problem seems to be related to Outer Joins. Here's a demo of the problem:

create table test1 (ukey integer not null default autoincrement primary key, SomeName char(30), Addr1 char(40), Addr2 char(40));
create table test2 (ukey integer not null default autoincrement primary key, Link1 integer, Data1 integer, Data2 integer, Data3 integer);
create table test3 (ukey integer not null default autoincrement primary key, Link1 integer, Data1 integer, Data2 integer, Data3 integer);

select test1.*, test2.data1, test3.data2 
from test1,
     test2
left outer join test3 on test3.link1=test1.ukey and test3.link1=test2.ukey;

Left outer join on clause fails to find Test1. If you flip Test1 and Test2 in the from clause, it will fail to find Test2 instead. Aliases don't help either. Why can't you refer to more than one other table in a join clause? Why would this work in an older version of SQLA and not now?

We have Developer SQLA17. I want to patch to current to test, but I have been unable to find anyone at SAP that can tell me how to get patches for developer SQLA17 so we can see if the problem has been fixed. We're in a catch-22 here - we won't buy without patching to test, and can't patch because we haven't bought it yet.

Does anyone have a clue as to what's wrong with this query? (demo is over-simplified, but a where/on clause referring to multiple other tables does not seem unusual to me) Or, if nothing is actually wrong, has it been fixed in a patch? We have dozens of datawindows that work this way, so a workaround isn't really an option here.

[VolkerBarth · Accepted solution]

I'm just another customer: Yes, Patches are no more free but the read me files are published (at least), see here - maybe you can try to find out if anything is changed in that respect. (The error, however, is also displayed with 17.0.10.5745, which is a very recent version...)

The comma is semantically similar to a CROSS JOIN but is not standardized AFAIK and has its pitfalls, see here.

So I would recommend to use ANSI join syntax for your case. At least the following is valid SQL:

select test1.*, test2.data1, test3.data2 
from test1 cross join 
     test2
left outer join test3 on test3.link1=test1.ukey and test3.link1=test2.ukey;

[Former Member · Accepted solution]

Using Cross Join works (so far).

I've successfully changed one of the failing selects and it looks like it's retrieving the same data.

I found another question (from 8 years ago) that basically asked the same thing. https://sqlanywhere-forum.sap.com/questions/5226/query-fails-in-asa-12-works-in-asa-8

Looks like parsing was tightened up in ASA12 and the old syntax (comma) no longer works when used with joins.

[former_SQLA_member1694885]

Looks like a precedence issue. Did you try to put brackets around (test1, test2)? If the left outer join has precedence over the "other" join (which is a cross join, btw), I'd expect this issue. I don't think its wise to mix the two join notations in a single statement anyway unless to confuse the Russians.
Would

select test1.*, test2.data1, test3.data2  
from (test1 join test2 on test1.ukey = test2.ukey)  
left outer join test3 on test3.link1=test1.ukey and test3.link1=test2.ukey

do the job and produce the correct / desired result?

HTH

Volker