Hello,
While looking into a particular SQL statement which seemed to put the
database under pressure, I found the following construct which is new to
me:
SELECT ...
FROM ...
OUTER UNION
SELECT ...
FROM ...
The statement was seen in the "Dynamic SQL statement text" part of "GET
SNAPSHOT FOR APPLICATION". In other words: The construct was accepted by
DB2. OUTER UNION doesn't seem to part of the SQL standard, and I can't
find documentation for it in DB2's documentation. Is this a documentation
defect, or is it simply me who's not good enough at searching the docs?
(The statement was issued from a SAS script, probably part of a SAS "proc
sql" section. OUTER UNION is documented in SAS' docs. I want to make sure
that SAS and DB2 agree on what's going on.)
--
Regards,
Troels Arvin <tr****@arvin.dk> http://troels.arvin.dk/ 10  5585 
Troels Arvin wrote:
Hello,
While looking into a particular SQL statement which seemed to put the
database under pressure, I found the following construct which is new to
me:
SELECT ...
FROM ...
OUTER UNION
SELECT ...
FROM ...
The statement was seen in the "Dynamic SQL statement text" part of "GET
SNAPSHOT FOR APPLICATION". In other words: The construct was accepted by
DB2. OUTER UNION doesn't seem to part of the SQL standard, and I can't
find documentation for it in DB2's documentation. Is this a documentation
defect, or is it simply me who's not good enough at searching the docs?
(The statement was issued from a SAS script, probably part of a SAS "proc
sql" section. OUTER UNION is documented in SAS' docs. I want to make sure
that SAS and DB2 agree on what's going on.)
db2 =connect to test;
Database Connection Information
Database server = DB2/NT 9.X.0
SQL authorization ID = SRIELAU
Local database alias = TEST
db2 =values 1 outer union values 1;
SQL0104N An unexpected token "outer" was found following "values 1 ".
Expected tokens may include: "<space>". SQLSTATE=42601
db2 =values 1 union values 1;
1
-----------
1
1 record(s) selected.
Which platform are you on? I have never heard of it. What would be it's
semantics?
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Serge Rielau wrote:
db2 =values 1 outer union values 1; SQL0104N An unexpected token
"outer" was found following "values 1 ". Expected tokens may include:
"<space>". SQLSTATE=42601 db2 =values 1 union values 1;
1
-----------
1
1 record(s) selected.
Which platform are you on?
DB2 v. 8 on AIX and DB2 v. 9.5 on Linux. On these platforms, your test
with VALUES ... VALUES also results in an error. But if I use real test
tables, "outer union" is accepted. It seems DB2 simply ignores the "outer"
word.
I have never heard of it. What would be it's semantics?
http://v8doc.sas.com/sashtml/proc/zueryexp.htm has some examples of it. If
my reading of SAS' docs is right, the semantics of SAS' OUTER UNION is
certainly not to simply ignore the OUTER word. SAS is surely being strange
regarding this (as it is with heaps of other subjects), but so is DB2: Why
doesn't it protest?
--
Regards,
Troels Arvin <tr****@arvin.dk> http://troels.arvin.dk/
Troels Arvin wrote:
Serge Rielau wrote:
>db2 =values 1 outer union values 1; SQL0104N An unexpected token
"outer" was found following "values 1 ". Expected tokens may include:
"<space>". SQLSTATE=42601 db2 =values 1 union values 1;
1
-----------
1
1 record(s) selected.
Which platform are you on?
DB2 v. 8 on AIX and DB2 v. 9.5 on Linux. On these platforms, your test
with VALUES ... VALUES also results in an error. But if I use real test
tables, "outer union" is accepted. It seems DB2 simply ignores the "outer"
word.
>I have never heard of it. What would be it's semantics?
http://v8doc.sas.com/sashtml/proc/zueryexp.htm has some examples of it. If
my reading of SAS' docs is right, the semantics of SAS' OUTER UNION is
certainly not to simply ignore the OUTER word. SAS is surely being strange
regarding this (as it is with heaps of other subjects), but so is DB2: Why
doesn't it protest?
Oh!, Ohh!
SELECT outer.c1 FROM T OUTER UNION VALUES 1;
:-)
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
The OUTER UNION is part of the infixed JOINs we define4d in SQL-99.
Nobody uses it,but it was easy to define, so it got into the
Standards. It can stick two totally unrelated tables together
blindly. http://www2.sas.com/proceedings/sugi31/242-31.pdf
--CELKO-- wrote:
The OUTER UNION is part of the infixed JOINs we define4d in SQL-99.
I can't find it in the draft for SQL:200n. Where in the SQL:1999 standard
is it mentioned?
--
Regards,
Troels Arvin <tr****@arvin.dk> http://troels.arvin.dk/
Troels Arvin wrote:
--CELKO-- wrote:
>The OUTER UNION is part of the infixed JOINs we define4d in SQL-99.
I can't find it in the draft for SQL:200n. Where in the SQL:1999 standard
is it mentioned?
Troels,
Did you see my post? DB2 interpretes the OUTER as a table alias.
T AS OUTER.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Serge Rielau wrote:
Oh!, Ohh!
SELECT outer.c1 FROM T OUTER UNION VALUES 1;
This was what I thought initially, as well (potentially very much
different from what the user expects after reading SAS' docs). But I've
seen OUTER UNION work with three different tables:
SELECT ...
FROM ...
OUTER UNION
SELECT ...
FROM ...
OUTER UNION
SELECT ...
FROM ...
Would DB2 allow re-use of table aliases?
--
Regards,
Troels Arvin <tr****@arvin.dk> http://troels.arvin.dk/
Troels Arvin wrote:
Would DB2 allow re-use of table aliases?
Yes, just try:
SELECT * FROM T AS X
UNION
SELECT * FROM T AS X
UNION
SELECT * FROM T AS X
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
On Aug 7, 2:19*pm, Troels Arvin <tro...@arvin.dkwrote:
--CELKO-- wrote:
The OUTER UNION is part of the infixed JOINs we define4d in SQL-99.
I can't find it in the draft for SQL:200n. Where in the SQL:1999 standard
is it mentioned?
I cant find it either and Mimers SQL Validator (http://
developer.mimer.com/validator/) doesn't recognize the construct.
CORRESPONDING is a reserved word though, but it where reserved back in
SQL92.
/Lennart
My bad! It is UNION JOIN in SQL-92, section 7.5
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Dave |
last post by:
Hi
I have the following 4 tables and I need to do a fully outerjoin on them.
create table A (a number, b number, c char(10), primary key (a,b))
create table B (a number, b number, c ...
|
by: Rahul Khandpur |
last post by:
hi friends,
i have one query related to oracle9i outer join condition
it is mentioned that we can't use (+) with the operand of OR
and IN operators
example
select...
|
by: alexcn |
last post by:
I have the following query:
SELECT dbo.tSymExch.exCode, dbo.tSymGrp.sgCode,
dbo.tSymMain.smCode
FROM dbo.tSymExch FULL OUTER JOIN
dbo.tSymGrp ON dbo.tSymExch.exID =...
|
by: KT |
last post by:
This might not be possible, but on the chance that it can - is there a
way to do the following:
Given a arbitray one dimesional value list:
('AALGX','12345','XXXXX','AAINX','AMMXX')
Is there...
|
by: dumbledad |
last post by:
Hi All,
I'm confused by how to replace a SELECT statement in a SQL statement
with a specific value. The table I'm working on is a list of words (a
column called "word") with an index int...
|
by: Randy Harris |
last post by:
Using an outer join, a query can return all records from Table1 and only
those matching from Table2 (or vice versa). How can I write a query that
will return unmatched records from both sides? ...
|
by: PW |
last post by:
I've created an ASP application which uses an Access database.
I've created an outer join query, but for some reason the "Property_Def"
column is not aligning with the "ESPN" column. They should...
|
by: gimme_this_gimme_that |
last post by:
I have an employee table with two columns, one named login, the other
named otherdata.
I have a list of login values, some of which do not exist in the
employee table.
I want to fetch the...
|
by: Hamayun Khan |
last post by:
Hi All
I am using the following query to select records from two tables.
Select JobTitle,JobDesc,Scraped,logoimage,JobPostID,SchoolID,web,MemType,InstitutionName,PayScale,LEA,Contract as...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
