View based on a UNION of 2 Select's

Thread View: borland.public.interbase.sql

5 messages

[T] Back to Threads [V] Tree View [G] Group Articles

5 total messages Started by Jose Carlos Wed, 11 Dec 2002 16:22

#11214

Author: Jose Carlos
Date: Wed, 11 Dec 2002 16:22

75 lines
2093 bytes

Hi All,

  I Have the following tables and view :

CREATE TABLE TAB1
(
  COD INTEGER NOT NULL,
  DESCR VARCHAR(10),
  CODTAB INTEGER,
  TYPETAB VARCHAR(1)
);

CREATE TABLE TAB2
(
  COD INTEGER NOT NULL,
  DESCR VARCHAR(10)
);

CREATE TABLE TAB3
(
  COD INTEGER NOT NULL,
  DESCR  VARCHAR(10)
);

CREATE VIEW V_TABS
(
  COD,
  DESCR,
  TYPETAB
)
AS
SELECT
  COD,
  DESCR,
  '2'
  FROM
    TAB2

UNION

SELECT
  COD,
  DESCR,
  '3'
  FROM
    TAB3;


  My problem happens when I try to run the following
select :

SELECT
    TAB1.COD,
    TAB1.DESCR,
    TAB1.TYPETAB || ' - ' || V_TABS.DESCR
  FROM
    TAB1
    LEFT OUTER JOIN V_TABS
    ON (  (V_TABS.CODTAB = TAB1.CODTAB)
        AND
          (V_TABS.TYPETAB = TAB1.TYPETAB))

  The select return the records I want but my plan analyser tool goes crazy
and show a lot of unrecognized characters instead of the correct plan. Am I
doing something wrong ? Can I trust that my select will always work ?

Is there some problem using a VIEW based on a UNION of two SELECT's ? Is it
safe ?

Do you have some other idea ?

Thanks for reading.

Jose Carlos
Brazil

Re: View based on a UNION of 2 Select's

#11215

Author: Martijn Tonies
Date: Wed, 11 Dec 2002 17:30

95 lines
2693 bytes

Hi,

What version of IB are you using? I recall a bug in earlier versions
that messed up the PLAN that IB returned.


--

With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase & Firebird
Firebird Workbench - the developer tool for Firebird
Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."

>
>   I Have the following tables and view :
>
> CREATE TABLE TAB1
> (
>   COD INTEGER NOT NULL,
>   DESCR VARCHAR(10),
>   CODTAB INTEGER,
>   TYPETAB VARCHAR(1)
> );
>
> CREATE TABLE TAB2
> (
>   COD INTEGER NOT NULL,
>   DESCR VARCHAR(10)
> );
>
> CREATE TABLE TAB3
> (
>   COD INTEGER NOT NULL,
>   DESCR  VARCHAR(10)
> );
>
> CREATE VIEW V_TABS
> (
>   COD,
>   DESCR,
>   TYPETAB
> )
> AS
> SELECT
>   COD,
>   DESCR,
>   '2'
>   FROM
>     TAB2
>
> UNION
>
> SELECT
>   COD,
>   DESCR,
>   '3'
>   FROM
>     TAB3;
>
>
>   My problem happens when I try to run the following
> select :
>
> SELECT
>     TAB1.COD,
>     TAB1.DESCR,
>     TAB1.TYPETAB || ' - ' || V_TABS.DESCR
>   FROM
>     TAB1
>     LEFT OUTER JOIN V_TABS
>     ON (  (V_TABS.CODTAB = TAB1.CODTAB)
>         AND
>           (V_TABS.TYPETAB = TAB1.TYPETAB))
>
>   The select return the records I want but my plan analyser tool goes crazy
> and show a lot of unrecognized characters instead of the correct plan. Am I
> doing something wrong ? Can I trust that my select will always work ?
>
> Is there some problem using a VIEW based on a UNION of two SELECT's ? Is it
> safe ?
>
> Do you have some other idea ?
>
> Thanks for reading.
>
> Jose Carlos
> Brazil
>
>

Re: View based on a UNION of 2 Select's

#11228

Author: Jose Carlos
Date: Thu, 12 Dec 2002 10:50

109 lines
3102 bytes

IB 6.0.2.0.
the same happens with 6.0.1.0 too.

thanks

Jose Carlos

"Martijn Tonies" <m.tonies@upscene_remove.com> escreveu na mensagem
news:3df775a5$1@newsgroups.borland.com...
> Hi,
>
> What version of IB are you using? I recall a bug in earlier versions
> that messed up the PLAN that IB returned.
>
>
> --
>
> With regards,
>
> Martijn Tonies
> InterBase Workbench - the developer tool for InterBase & Firebird
> Firebird Workbench - the developer tool for Firebird
> Upscene Productions
> http://www.upscene.com
>
> "This is an object-oriented system.
> If we change anything, the users object."
>
> >
> >   I Have the following tables and view :
> >
> > CREATE TABLE TAB1
> > (
> >   COD INTEGER NOT NULL,
> >   DESCR VARCHAR(10),
> >   CODTAB INTEGER,
> >   TYPETAB VARCHAR(1)
> > );
> >
> > CREATE TABLE TAB2
> > (
> >   COD INTEGER NOT NULL,
> >   DESCR VARCHAR(10)
> > );
> >
> > CREATE TABLE TAB3
> > (
> >   COD INTEGER NOT NULL,
> >   DESCR  VARCHAR(10)
> > );
> >
> > CREATE VIEW V_TABS
> > (
> >   COD,
> >   DESCR,
> >   TYPETAB
> > )
> > AS
> > SELECT
> >   COD,
> >   DESCR,
> >   '2'
> >   FROM
> >     TAB2
> >
> > UNION
> >
> > SELECT
> >   COD,
> >   DESCR,
> >   '3'
> >   FROM
> >     TAB3;
> >
> >
> >   My problem happens when I try to run the following
> > select :
> >
> > SELECT
> >     TAB1.COD,
> >     TAB1.DESCR,
> >     TAB1.TYPETAB || ' - ' || V_TABS.DESCR
> >   FROM
> >     TAB1
> >     LEFT OUTER JOIN V_TABS
> >     ON (  (V_TABS.CODTAB = TAB1.CODTAB)
> >         AND
> >           (V_TABS.TYPETAB = TAB1.TYPETAB))
> >
> >   The select return the records I want but my plan analyser tool goes
crazy
> > and show a lot of unrecognized characters instead of the correct plan.
Am I
> > doing something wrong ? Can I trust that my select will always work ?
> >
> > Is there some problem using a VIEW based on a UNION of two SELECT's ? Is
it
> > safe ?
> >
> > Do you have some other idea ?
> >
> > Thanks for reading.
> >
> > Jose Carlos
> > Brazil
> >
> >
>
>

Re: View based on a UNION of 2 Select's

#11232

Author: Martijn Tonies
Date: Thu, 12 Dec 2002 13:08

129 lines
3875 bytes

Hi Jose,

You might want to try with IB 6.5 or 7 when the download comes available.


--

With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase & Firebird
Firebird Workbench - the developer tool for Firebird
Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."

> IB 6.0.2.0.
> the same happens with 6.0.1.0 too.
>
> thanks
>
> Jose Carlos
>
> "Martijn Tonies" <m.tonies@upscene_remove.com> escreveu na mensagem
> news:3df775a5$1@newsgroups.borland.com...
> > Hi,
> >
> > What version of IB are you using? I recall a bug in earlier versions
> > that messed up the PLAN that IB returned.
> >
> >
> > --
> >
> > With regards,
> >
> > Martijn Tonies
> > InterBase Workbench - the developer tool for InterBase & Firebird
> > Firebird Workbench - the developer tool for Firebird
> > Upscene Productions
> > http://www.upscene.com
> >
> > "This is an object-oriented system.
> > If we change anything, the users object."
> >
> > >
> > >   I Have the following tables and view :
> > >
> > > CREATE TABLE TAB1
> > > (
> > >   COD INTEGER NOT NULL,
> > >   DESCR VARCHAR(10),
> > >   CODTAB INTEGER,
> > >   TYPETAB VARCHAR(1)
> > > );
> > >
> > > CREATE TABLE TAB2
> > > (
> > >   COD INTEGER NOT NULL,
> > >   DESCR VARCHAR(10)
> > > );
> > >
> > > CREATE TABLE TAB3
> > > (
> > >   COD INTEGER NOT NULL,
> > >   DESCR  VARCHAR(10)
> > > );
> > >
> > > CREATE VIEW V_TABS
> > > (
> > >   COD,
> > >   DESCR,
> > >   TYPETAB
> > > )
> > > AS
> > > SELECT
> > >   COD,
> > >   DESCR,
> > >   '2'
> > >   FROM
> > >     TAB2
> > >
> > > UNION
> > >
> > > SELECT
> > >   COD,
> > >   DESCR,
> > >   '3'
> > >   FROM
> > >     TAB3;
> > >
> > >
> > >   My problem happens when I try to run the following
> > > select :
> > >
> > > SELECT
> > >     TAB1.COD,
> > >     TAB1.DESCR,
> > >     TAB1.TYPETAB || ' - ' || V_TABS.DESCR
> > >   FROM
> > >     TAB1
> > >     LEFT OUTER JOIN V_TABS
> > >     ON (  (V_TABS.CODTAB = TAB1.CODTAB)
> > >         AND
> > >           (V_TABS.TYPETAB = TAB1.TYPETAB))
> > >
> > >   The select return the records I want but my plan analyser tool goes
> crazy
> > > and show a lot of unrecognized characters instead of the correct plan.
> Am I
> > > doing something wrong ? Can I trust that my select will always work ?
> > >
> > > Is there some problem using a VIEW based on a UNION of two SELECT's ? Is
> it
> > > safe ?
> > >
> > > Do you have some other idea ?
> > >
> > > Thanks for reading.
> > >
> > > Jose Carlos
> > > Brazil
> > >
> > >
> >
> >
>
>

Re: View based on a UNION of 2 Select's

#11235

Author: Ivan Prenosil
Date: Thu, 12 Dec 2002 13:39

14 lines
1416 bytes

>   My problem happens when I try to run the following
> select :
...
>
>   The select return the records I want but my plan analyser tool goes crazy
> and show a lot of unrecognized characters instead of the correct plan. Am I
> doing something wrong ? Can I trust that my select will always work ?

Is not the problem with your "plan analyser tool" ?
Have you tried standard plain ISQL ?

--
Ivan
http://www.volny.cz/iprenosil/interbase

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads