The original homework assignment was probably, "What is wrong with this
SQL?"
Michel Cadot wrote in message <8fp9ab$2nm2$1_at_news4.isdnet.net>...
>First you haven't created your view
>with the statement you give us.
>Actually, in your create view statement
>view keyword is missing and there is 4
>columns for the view and only 3 in the
>select statements.
>
>If you correct that, it's important to
>know your Oracle version.
>
>That works fine in Oracle 7.3.4, with
>union or union all:
>
>v734> create table tablea (col1 number,colx number);
>v734> create table tableb (col2 number, col3 number);
>v734> create or replace view my_view (col1,col2,col3) as
> 2 select a.col1,b.col2,b.col3
> 3 from tablea a
> 4 , (select col2
> 5 , col3
> 6 from tableb) b
> 7 where b.col3 = a.colx
> 8 union all
> 9 select a.col1,b.col2,b.col3
> 10 from tablea a
> 11 , (select col2
> 12 , col3
> 13 from tableb) b
> 14 where b.col3 = a.colx
> 15 /
>v734> select 1, 2 from my_view;
>
>no rows selected
>
>v734> select * from my_view;
>
>no rows selected
>
>v734> create or replace view my_view (col1,col2,col3) as
> 2 select a.col1,b.col2,b.col3
> 3 from tablea a
> 4 , (select col2
> 5 , col3
> 6 from tableb) b
> 7 where b.col3 = a.colx
> 8 union
> 9 select a.col1,b.col2,b.col3
> 10 from tablea a
> 11 , (select col2
> 12 , col3
> 13 from tableb) b
> 14 where b.col3 = a.colx
> 15 /
>
>v734> select 1, 2 from my_view;
>
>no rows selected
>
>v734> select * from my_view;
>
>no rows selected
>
>
>But you're right in 8.1.5:
>
>v815> create table tablea (col1 number,colx number);
>v815> create table tableb (col2 number, col3 number);
>v815> create or replace view my_view (col1,col2,col3) as
> 2 select a.col1,b.col2,b.col3
> 3 from tablea a
> 4 , (select col2
> 5 , col3
> 6 from tableb) b
> 7 where b.col3 = a.colx
> 8 union all
> 9 select a.col1,b.col2,b.col3
> 10 from tablea a
> 11 , (select col2
> 12 , col3
> 13 from tableb) b
> 14 where b.col3 = a.colx
> 15 /
>v815> select 1, 2 from my_view;
>select 1, 2 from my_view
> *
>ERROR at line 1:
>ORA-01789: query block has incorrect number of result columns
>
>v815> select * from my_view;
>
>no rows selected
>
>v815> create or replace view my_view (col1,col2,col3) as
> 2 select a.col1,b.col2,b.col3
> 3 from tablea a
> 4 , (select col2
> 5 , col3
> 6 from tableb) b
> 7 where b.col3 = a.colx
> 8 union
> 9 select a.col1,b.col2,b.col3
> 10 from tablea a
> 11 , (select col2
> 12 , col3
> 13 from tableb) b
> 14 where b.col3 = a.colx
> 15 /
>
>v815> select 1, 2 from my_view;
>
>no rows selected
>
>v815> select * from my_view;
>
>no rows selected
>
>The difference between union and union all is
>the deletion of the duplicated rows with the union.
>
>Here's the two explain plans for
>"select * from my_view;"
>first with union all and then with union.
>
>SELECT STATEMENT
> VIEW MY_VIEW
> UNION-ALL
> MERGE JOIN
> SORT JOIN
> TABLE ACCESS FULL TABLEB
> SORT JOIN
> TABLE ACCESS FULL TABLEA
> MERGE JOIN
> SORT JOIN
> TABLE ACCESS FULL TABLEB
> SORT JOIN
> TABLE ACCESS FULL TABLEA
>
>SELECT STATEMENT
> VIEW MY_VIEW
> SORT UNIQUE
> UNION-ALL
> MERGE JOIN
> SORT JOIN
> TABLE ACCESS FULL TABLEB
> SORT JOIN
> TABLE ACCESS FULL TABLEA
> MERGE JOIN
> SORT JOIN
> TABLE ACCESS FULL TABLEB
> SORT JOIN
> TABLE ACCESS FULL TABLEA
>
>You see the SORT UNIQUE step used for the deletion
>of the duplicates.
>
>It is interesting to notice that the use
>of an inline view does not generate an error:
>
>v815> select 1, 2 from
> 2 (select a.col1,b.col2,b.col3
> 3 from tablea a
> 4 , (select col2
> 5 , col3
> 6 from tableb) b
> 7 where b.col3 = a.colx
> 8 union all
> 9 select a.col1,b.col2,b.col3
> 10 from tablea a
> 11 , (select col2
> 12 , col3
> 13 from tableb) b
> 14 where b.col3 = a.colx) ;
>
>no rows selected
>
>
>So, it seems to be a bug.
>
>--
>Regards
>Michel
>
>
>Gareth Leachman <grleachman_at_my-deja.com> a écrit dans le message :
8fp5sa$dq9$1_at_nnrp1.deja.com...
>> 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
