Path: utzoo!attcan!uunet!tut.cis.ohio-state.edu!uccba!mead!gordon
From: gordon@mead.UUCP (Gordon Edwards)
Newsgroups: comp.databases
Subject: Re: UNIONS/VIEWS
Keywords: ingres
Message-ID: <1064@meaddata.mead.UUCP>
Date: 3 Aug 90 13:37:33 GMT
References: <5670@rtech.Ingres.COM>
Sender: usenet@mead.UUCP
Reply-To: mead!gordon@uccba.uc.edu
Distribution: na
Organization: Mead Data Central, Dayton OH
Lines: 71


In article <5670@rtech.Ingres.COM>, robf@squid.rtech.com (Robert Fair) writes:
|> Ingres 6.x SQL *does* handle view definitions containing UNION and
UNION ALL,
|> e.g.
|> 	CREATE VIEW a_and_b
|> 	AS
|> 		SELECT x FROM a
|> 	UNION 
|> 		SELECT x FROM b
|> 
|> works just fine.
|> 

No one is questioning the ability to create a view containing a UNION.  Can
the view be updated is the question.  

----

1)  ANSI says, "Just say no."

(ANSI X3.135-1989 pages 53, 66, 79)

<view definition> ::= 
	CREATE VIEW <table name> [(<view column list>)]
	AS <query specification>
	[WITH CHECK OPTION]

Syntax Rule 3) If the <query specification> is updatable, then the viewed 
table is an updatable table.  Otherwise, it is a read-only table.


<query expression> ::=
	<query term> | <query expression> UNION [ALL] <query term>

<query term> ::= 
	<query specification> | (<query expression>)

<query specification> ::=
	SELECT [ALL | DISTINCT] <select list> <table expression>

<select list) ::=
	<value expression> [{, <value expression>}...] | *

----

2)  My previous post argues the case from a logical point of view.  To be
updatable, the INSERT, UPDATE, and DELETE operations have to be defined.  If
the view is created with a UNION, which table will a tuple be inserted?

Ingres can't update a view created with a UNION!   I tried using
Ingres 6.3, and it came back and told me that the update was illegal.

INSERT INTO v1 VALUES (10, 10)

returned...

E_US0D21 INSERT on view v1: cannot update this view.)
    (Fri Aug  3 09:31:55 1990)

----

I made a subtle error in my previous post on this thread.  I said that neither
Ingres or *SYBASE* supports this feature.  Sybase doesn't support views at all
in the current release (yes, I know it's going to be fixed).

----
Gordon Edwards                                    Mead Data Central, Dayton OH

Please, post responses.  Our mail feed will be changing any day now, and my
e-mail address may not work.