While I'm about to leave ASA 8.0.3 behind, I made some comparisons with ASA 8.0.3.5574 and SA 12.0.0.2566 and accidently used EXCEPT and INTERSECT on ASA 8.0.3.

I had strongly expected a syntax error as I was absolutely sure both set operators were introduced with ASA9 (and they are not at all documented with ASA 8), but they work.

Just couldn't believe it: Have I deliberately - but unfounded! - left out these handy operators all those years when working with ASA 8 (while using them on newer version)?

Or have I missed a relevant piece of information?


But wait and calm down, Volker - they seem to get accepted syntactically in ASA 8.0.3 but don't seem to work correctly, i.e. a

SELECT pk_Column FROM table1 EXCEPT SELECT pk_Column FROM table2

does return the same resultset as without the EXCEPT clause or with an INTERSECT clause.

So it seems I have not missed an important feature...

My pulse is still accelerating...

asked 22 Sep '10, 15:41

Volker%20Barth's gravatar image

Volker Barth
39.7k357545814
accept rate: 34%

edited 23 Sep '10, 13:27

1

Shh, don't tell anyone, but historically speaking the parser has been known to allow syntax for which no semantics exist :)

(22 Sep '10, 16:17) Breck Carter

In version 8, EXCEPT would not have been recognized as a keyword. In your example, it would have been treated as an identifier, specifically an alias for table1. The line:

SELECT pk_Column FROM table1 EXCEPT SELECT pk_Column FROM table2

would have been treated as a Transact-SQL batch returning two results sets. If you execute it in DBISQLC and then enter "resume", you'll see the second result set.

permanent link

answered 24 Sep '10, 13:32

Bruce%20Hay's gravatar image

Bruce Hay
2.6k1510
accept rate: 48%

Ah, I see - I had not thought of the T-SQL batch feature but that makes it very reasonable that my sample statement worked whereas more complex ones (i.e. use this statement as a derived table) gives a syntax error. - Well, better SQL dialects use statement delimiters, don't they:)

(24 Sep '10, 16:01) Volker Barth

Just to complete and answer the title question (whereas Bruce has answered the real question):

As already stated in my question - both set operators were introduced with ASA 9.0.

permanent link

answered 24 Sep '10, 16:10

Volker%20Barth's gravatar image

Volker Barth
39.7k357545814
accept rate: 34%

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:

×107
×33
×5
×2

question asked: 22 Sep '10, 15:41

question was seen: 3,581 times

last updated: 24 Sep '10, 16:10