Union All Problem

From: Gareth Leachman <grleachman_at_my-deja.com>
Date: 2000/05/15
Message-ID: <8fp5sa$dq9$1@nnrp1.deja.com>#1/1

I would like to know if anyone has a documented reason for the following:

select 1, 2 from my_view;
select * from my_view;

are 2 select queries on my_view.
my_view defined as:

create my_view (col1,col2,col3,col4) as
select a.col1,b.col2,b.col3
from tablea a
, (select col2

      ,      col3
      from tableb) b

where b.col3 = a.colx
union all
select a.col1,b.col2,b.col3
from tablea a
, (select col2

      ,      col3
      from tableb) b

where b.col3 = a.colx

Now when the first query is run it gives error 1789: Query block has incorrect number of result columns.
The second query returns a correct result.

After doing a little bit of fiddling, the inline view was taken out, and this produced a proper result for query 1.

It was interesting to note that replacing union all with union worked for query 1 and 2 in the original version.

So the question is: why would an inline view affect the result of a union all query.

Thanks
Gareth

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon May 15 2000 - 00:00:00 CDT