Using Union subquery in select clause 
Author Message
 Using Union subquery in select clause
Can I use union subquery in select clause for SQL/400?

Something like

select * from (select a from abc union b from abc1) tbl where tbl.a =
'*'

All my attemps generated a syntax error.

Thank you,

Pavel

Sent via Deja.com
http://www.***.com/



Sat, 19 Jul 2003 22:41:28 GMT
 Using Union subquery in select clause

Quote:

> Can I use union subquery in select clause for SQL/400?

> Something like

> select * from (select a from abc union b from abc1) tbl where tbl.a =
> '*'

Two mistakes. First you miss a select keyword. Second you have to define
the same
column name for each corresponding column. That is:

select * from (select a as a from abc union select b as a from abc1) tbl
where tbl.a = '*'

Obviously that are constraints about the type of the column, but you can
cast them when needed.
As last, use union all if you don't want db2 to eliminate duplicate rows
(much faster for big tables).

Hope it helps.
Matteo

--
Matteo Gelosa                                             I.NET S.p.A.
Research & Development      Via Darwin, 85 - 20090 Settimo Milanese MI

http://www.inet.it                               Fax +39.02.32863.7701



Sun, 20 Jul 2003 17:53:48 GMT
 Using Union subquery in select clause
The example of a query was very schematical - not real. Even if you
will fix all syntax errors it still won't work because of the UNION key
word. Otherwords if you remove UNION and one of the select statements
it links everything is working fine. You put it back - it doesn't like
it.
Is it generally possible to use UNION in subqueries? I can do it on
Oracle.

Thank you,

Pavel



Quote:

> > Can I use union subquery in select clause for SQL/400?

> > Something like

> > select * from (select a from abc union b from abc1) tbl where tbl.a
=
> > '*'

> Two mistakes. First you miss a select keyword. Second you have to
define
> the same
> column name for each corresponding column. That is:

> select * from (select a as a from abc union select b as a from abc1)
tbl
> where tbl.a = '*'

> Obviously that are constraints about the type of the column, but you
can
> cast them when needed.
> As last, use union all if you don't want db2 to eliminate duplicate
rows
> (much faster for big tables).

> Hope it helps.
> Matteo

> --
> Matteo Gelosa                                             I.NET S.p.A.
> Research & Development      Via Darwin, 85 - 20090 Settimo Milanese MI

> http://www.inet.it                               Fax +39.02.32863.7701

Sent via Deja.com
http://www.deja.com/


Mon, 21 Jul 2003 03:52:17 GMT
 Using Union subquery in select clause
The example works quite fine, i.e. it is possible to use a UNION within
subselects.

What is your exact error message and the exact SQL SELECT statement you try
to run?

Quote:

> The example of a query was very schematical - not real. Even if you
> will fix all syntax errors it still won't work because of the UNION key
> word. Otherwords if you remove UNION and one of the select statements
> it links everything is working fine. You put it back - it doesn't like
> it.
> Is it generally possible to use UNION in subqueries? I can do it on
> Oracle.

--
Knut Stolze
DB2 UDB Spatial Extender
IBM Silicon Valley Lab


Fri, 25 Jul 2003 05:39:45 GMT
 Using Union subquery in select clause
DB2 UDB for iSeries does not currently suppport the UNION keyword in
subqueries or in nested table expressions.  This is a known requirement.

Carol Ramler, DB2 UDB for iSeries Development

Quote:

> Can I use union subquery in select clause for SQL/400?

> Something like

> select * from (select a from abc union b from abc1) tbl where tbl.a =
> '*'

> All my attemps generated a syntax error.

> Thank you,

> Pavel

> Sent via Deja.com
> http://www.deja.com/



Sat, 26 Jul 2003 02:30:55 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. results from SELECT clause used in an IN clause

2. ANSI syntax for a correlated subquery in the SELECT clause

3. Subquery on SELECT clause

4. Subqueries in select clause

5. Outer reference in SELECT clause of subquery

6. using recordset(subquery) as target of FROM clause

7. UPDATE Query - using Subquery and Top Clause

8. Using UNION and ORDER BY clauses

9. SQL question: using UNION in FROM clause with DB2 V5

10. Subquery in Select list item aliased and used in expression

11. cursor using select from subquery

12. Select Into and using UNION statement


 
Powered by phpBB® Forum Software