Re: View updating in practice?
Date: Wed, 13 Nov 2002 11:51:01 -0800
Mikito Harakiri wrote:
> "Jens Lechtenbörger <lechtej_at_uni-muenster.de> wrote in message news:<m28yzxhlih.fsf_at_pcwi1068.uni-muenster.de>...
> > > Relation A:
> > >
> > > ID VOICE FAX
> > > -- ----------- --------------
> > > 1 4150000000 4081111111
> > > 2 80012345672 6501234567
> > >
> > > select id, 'VOICE' phonetype, voice number
> > > from A
> > > union
> > > select id, 'FAX' phonetype, fax number from A
> > >
> > > What is the method to invert it?
> > It should be a join of (a) the projection of the view on id and
> > voice_number and (b) the projection of the view on id and
> > fax_number, i.e., something like this:
> > select v1.id, v1.voice_number, v2.fax_number
> > from
> > (select id, voice_number from view) as v1,
> > (select id, fax_number from view) as v2
> > where v1.id=v2.id
Not exactly correct, but close. 2 predicates need to be added:
select a.id, a.number voice, b.number fax
from view a, view b
where a.id = b.id
and a.phonetype = 'VOICE'
and b.phonetype = 'FAX';
(The answer is copied-and-pasted from that old thread "inverse view", of course;-) Received on Wed Nov 13 2002 - 20:51:01 CET