Re: Create View with a UNION ?
Well,
That select can be implemented WITHOUT a union, so it may be the optimizer
is allowing it.
SELECT tabid from systables Where tabid >50 or tabid<99
is equivalent.
And, that is equivalent to :
SELECT tabid from systables.
"COOPER, Joseph" <Joseph.COOPER@sema.co.uk> wrote in message
news:8a3bnl$mgc$1@news.xmission.com...
>
>
>
> > -----Original Message-----
> > From: Gabbard, Mike W. [mailto:MikeG@red-man.com]
> > Sent: 07 March 2000 13:56
> > To: 'Gail'; informix-list@iiug.org
> > Subject: RE: Create View with a UNION ?
> >
> >
> > No it can't be done...............
>
> Well I can create a view called v_joe using the following SQL
>
> CREATE view v_joe ( val1 )> AS SELECT tabid FROM systables WHERE tabid > 50
> UNION SELECT tabid FROM systables WHERE tabid < 99
>
> And it passes all syntax checks.
>
> Jo
>
>
> >
> > -----Original Message-----
> > From: Gail [mailto:wormang@sutterhealth.org]
> > Sent: Monday, March 06, 2000 8:08 PM
> > To: informix-list@iiug.org
> > Subject: Create View with a UNION ?
> >
> >
> > Hello All,
> > Can you create a view using a UNION statement? I'm new at
> > writing Informix
> > SQL and I am receiving an error when I execute the SQL below.
> > Any help will
> > be appreciated.
> >
> > Thanks!
> > Gail
> >
> > CREATE VIEW testgail
> > (activity,
> > fiscal_year,
> > period,
> > r_system,
> > atn_obj_id,
> > tran_amount)> > AS SELECT
> > accommitx.activity,
> > accommitx.fiscal_year,
> > accommitx.period,
> > accommitx.r_system,
> > accommitx.atn_obj_id,
> > accommitx.tran_amount
> > FROM
> > xxxx.accommitx accommitx
> > WHERE
> > accommitx.activity = '47049009'
> > UNION
> > AS SELECT
> > actrans.activity,
> > actrans.fiscal_year,
> > actrans.period,
> > actrans.r_system,
> > actrans.obj_id,
> > actrans.tran_amount
> > FROM
> > xxxx.actrans actrans
> > WHERE
> > actrans.activity = '47049009';
> >
> > REVOKE ALL
> > on testgail
> > from public ;
> >
> > Grant Select
> > on testgail
> > to public;
> >
> >
> >
> >
>
>
___________________________________________________________________________
> This email is confidential and intended solely for the use of the
> individual to whom it is addressed. Any views or opinions presented are
> solely those of the author and do not necessarily represent those of
> Sema Group.
> If you are not the intended recipient, be advised that you have received
this
> email in error and that any use, dissemination, forwarding, printing, or
> copying of this email is strictly prohibited.
>
> If you have received this email in error please notify the Sema Group
> Helpdesk by telephone on +44 (0) 121 627 5600.
>
___________________________________________________________________________
>