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"

asked 04 Mar, 10:49

juysu2's gravatar image

juysu2
2614
accept rate: 0%

edited 04 Mar, 10:52

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...

(04 Mar, 12:42) Volker Barth
Replies hidden

> Does the SELECT work without unload?

IMO that is the bug... it shouldn't work, but it does :)

(04 Mar, 14:14) Breck Carter

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

answered 04 Mar, 14:13

Breck%20Carter's gravatar image

Breck Carter
29.3k486650958
accept rate: 20%

I'd also omit the parantheses around the join condition... :)

(04 Mar, 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.

permanent link

answered 05 Mar, 01:08

juysu2's gravatar image

juysu2
2614
accept rate: 0%

That's why I had written

I think your view's FROM clause is written rather unorthodox

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.


I still think it's a bug.

As the view's definition is allowed, I agree that UNLOAD should work there, too.

(05 Mar, 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, 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:

The duplicate names must be in different joins for the syntax to make sense. When a table name or view name is used twice in the same join, the second instance is ignored. For example, FROM A,A and FROM A CROSS JOIN A are both interpreted as FROM A.

Yes, I'm reading out the docs, I'm aware that this is not at all the same as explaining the topic...

(05 Mar, 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, 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, 09:04) Breck Carter
3

> for a reason:)

You're not a dummy, dummy, dummy, dummy, dummy :)

(05 Mar, 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, 03:37) Volker Barth
showing 1 of 7 show all flat view
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:

×56
×43
×40

question asked: 04 Mar, 10:49

question was seen: 259 times

last updated: 06 Mar, 03:37