|
SQLANYWHERE 17.0.4.2053 DEVELOPER EDITION DROP VARIABLE IF EXISTS @response; CREATE VARIABLE @response long nvarchar; DROP TABLE IF EXISTS a; DROP TABLE IF EXISTS b; DROP TABLE IF EXISTS c; CREATE TABLE a ( id_key integer ); CREATE TABLE b ( id_key integer, id_table_a integer ); CREATE TABLE c ( id_key integer, id_table_a integer ); INSERT INTO a VALUES ( 1 ); INSERT INTO b VALUES ( 1, 1 ); INSERT INTO c VALUES ( 1, 1 ); CREATE OR REPLACE VIEW v AS SELECT a.id_key AS a1, b.id_key AS b1, c.id_key AS c1 FROM a INNER JOIN b ON ( a.id_key = b.id_table_a ), a INNER JOIN c ON ( a.id_key = c.id_table_a ) ; UNLOAD SELECT * FROM v INTO VARIABLE @response; -- Could not execute statement. -- Illegal reference to correlation name 'a' -- SQLCODE=-824, ODBC 3 State="42S02" |
|
To expand on what Volker said... Your view SELECT has two references to table a without any alias name: CREATE OR REPLACE VIEW v AS SELECT a.id_key AS a1, b.id_key AS b1, c.id_key AS c1 FROM a INNER JOIN b ON ( a.id_key = b.id_table_a ), a INNER JOIN c ON ( a.id_key = c.id_table_a ) ; Try simplifying the FROM clause to get rid of the extra a and the superfluous comma-join operator: CREATE OR REPLACE VIEW v AS SELECT a.id_key AS a1, b.id_key AS b1, c.id_key AS c1 FROM a INNER JOIN b ON ( a.id_key = b.id_table_a ) INNER JOIN c ON ( a.id_key = c.id_table_a ) ; I'd also omit the parantheses around the join condition... :)
(04 Mar '19, 16:00)
Volker Barth
|
|
The view is Ok. select * from v; a1 b1 c1 1 1 1 "Your view SELECT has two references to table a without any alias name:", See: http://dcx.sap.com/index.html#sqla170/en/html/81858d366ce21014b644dd6961b5a41f.html I still think it's a bug. That's why I had written
but not wrong... Is there a reason why you do not use two simple ANSI inner joins here? AFAIK, the star join syntax mentioned in the link is basically a Transact-SQL/ASE compatibility feature usually only needed there for particular complex cases of outer joins (which AFAIK can always be specified using ANSI JOIN syntax in SQL Anywhere). For a simple inner join between three tables, there is no need to use that unusual syntax IMHO. As Breck has shown, the view written with two common inner join conditions does not lead to that error with UNLOAD.
As the view's definition is allowed, I agree that UNLOAD should work there, too.
(05 Mar '19, 04:00)
Volker Barth
Replies hidden
Comment Text Removed
Challenge: Explain why the following SELECT is valid, what it means, and why the result set is correct :) select * from dummy, dummy, dummy, dummy, dummy; dummy_col ----------- 0 (1 rows) Execution time: 0.015 seconds
(05 Mar '19, 07:55)
Breck Carter
Easy: select rewrite('select * from dummy, dummy, dummy, dummy, dummy;')
returns select dummy.dummy_col from dummy It's also documented in the mentioned doc page:
Yes, I'm reading out the docs, I'm aware that this is not at all the same as explaining the topic...
(05 Mar '19, 08:13)
Volker Barth
To add: I'm sure we both agree one should not write such kind of queries, and I have never used syntax like in this question, for a reason:)
(05 Mar '19, 08:14)
Volker Barth
When someone codes "FROM A CROSS JOIN A" that is clearly a mistake, and it should raise an error so the mistake will be noticed and fixed. At least, that's what The Watcom Rule implies :) I make lots and lots of mistakes, and I don't want any of them to be ignored.
(05 Mar '19, 09:04)
Breck Carter
3
> for a reason:) You're not a dummy, dummy, dummy, dummy, dummy :)
(05 Mar '19, 09:05)
Breck Carter
Hm, that's probably an academic question: As a cross join returns the combination of every row from the first table and of the second table, what does that mean if it is the same table, and no alias is used to specify that two instances of the same table are meant? - I do not know the ANSI specs but IMHO it seems reasonable to return just all rows of the one and only table then, as SQL Aynwhere apparently does. I agree that this may come as a surprise but I use cross joins rather rarely so I would not consider that a "typical mistake"...
(06 Mar '19, 03:37)
Volker Barth
|
Does the SELECT work without unload? Does a simple select from your view work?
I think your view's FROM clause is written rather unorthodox... If you just want to join a to b and c, two simple inner join conditions would be sufficient, no need for brackets or a comma operator...
> Does the SELECT work without unload?
IMO that is the bug... it shouldn't work, but it does :)