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.

asked 16 May, 09:35

BradM's gravatar image

BradM
465
accept rate: 0%


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;
permanent link

answered 16 May, 12:18

Volker%20Barth's gravatar image

Volker Barth
33.8k330486716
accept rate: 33%

converted 16 May, 12:19

1

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.

(16 May, 14:03) BradM

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

permanent link

answered 16 May, 12:25

Volker%20DB-TecKy's gravatar image

Volker DB-TecKy
4703513
accept rate: 28%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×33
×11
×3

question asked: 16 May, 09:35

question was seen: 107 times

last updated: 16 May, 14:03