Professional Web Applications Themes

SQL construct not supported in informix ? - Informix

On Thu, 17 Jul 2003 11:40:27 -0400, vk02720 wrote: > The following SQL construct does not work in Informix 7.3 select a,b > from > (select col1 a, col2 b from mytable) > > This works in Oracle. > Is this part of SQL standard or an Oracle extension ? > > TIA Non-standard to the level of SQL that Informix supports. Art S. Kagel...

  1. #1

    Default Re: SQL construct not supported in informix ?

    On Thu, 17 Jul 2003 11:40:27 -0400, vk02720 wrote:
    > The following SQL construct does not work in Informix 7.3 select a,b
    > from
    > (select col1 a, col2 b from mytable)
    >
    > This works in Oracle.
    > Is this part of SQL standard or an Oracle extension ?
    >
    > TIA
    Non-standard to the level of SQL that Informix supports.


    Art S. Kagel
    Art S. Kagel Guest

  2. #2

    Default Re: SQL construct not supported in informix ?

    "Art S. Kagel" <kagelbloomberg.net> wrote in message news:<pan.2003.07.17.18.01.18.408954.15473bloombe rg.net>...
    > On Thu, 17 Jul 2003 11:40:27 -0400, vk02720 wrote:
    >
    > > The following SQL construct does not work in Informix 7.3 select a,b
    > > from
    > > (select col1 a, col2 b from mytable)
    > >
    > > This works in Oracle.
    > > Is this part of SQL standard or an Oracle extension ?
    > >
    > > TIA
    >
    > Non-standard to the level of SQL that Informix supports.
    >
    >
    > Art S. Kagel
    Thanks.
    So what level of SQL standard does Informix support ? And again, is
    this construct a SQL standard ( as compared to purely an Oracle
    extension ) ?
    Any good website for SQL standard ?
    vk02720 Guest

  3. #3

    Default Re: SQL construct not supported in informix ?

    [email]vk02720my-deja.com[/email] (vk02720) wrote in message news:<4d814faa.0307170740.3e983552posting.google. com>...
    > The following SQL construct does not work in Informix 7.3
    > select a,b from
    > (select col1 a, col2 b from mytable)
    >
    > This works in Oracle.
    > Is this part of SQL standard or an Oracle extension ?
    >
    > TIA

    You can achieve the same result by doing: select a, b from
    Table(Multiset(select col1 as a, col2 as b from mytable)) as foo

    However, some other databases would try to expand the subquery and
    find more efficient joins to table in the outer query if there are
    any. Informix won't.
    Kristofer Andersson Guest

  4. #4

    Default Re: SQL construct not supported in informix ?


    Intereting...

    Does it work in IDS 9.3 too?

    I think the same could be achieved by creating a temporary table:

    select col1 as a, col2 as b from mytable into temp tx with no log;
    select a,b from tx;

    Questions:

    - Wich is better?
    - Why I may want to use a FROM like that?

    Chucho!



    Kristofer Andersson wrote:
    > [email]vk02720my-deja.com[/email] (vk02720) wrote in message news:<4d814faa.0307170740.3e983552posting.google. com>...
    >
    >>The following SQL construct does not work in Informix 7.3
    >>select a,b from
    >>(select col1 a, col2 b from mytable)
    >>
    >>This works in Oracle.
    >>Is this part of SQL standard or an Oracle extension ?
    >>
    >>TIA
    >
    >
    >
    > You can achieve the same result by doing: select a, b from
    > Table(Multiset(select col1 as a, col2 as b from mytable)) as foo
    >
    > However, some other databases would try to expand the subquery and
    > find more efficient joins to table in the outer query if there are
    > any. Informix won't.
    >
    >

    --


    Atte,


    Jesús Antonio Santos Giraldo
    [email]jeansagimyrealbox.com[/email]
    [email]jeansaginetscape.net[/email]

    sending to informix-list
    Jean Sagi Guest

  5. #5

    Default Re: SQL construct not supported in informix ?

    Jean Sagi <jeansagimyrealbox.com> wrote in message news:<bfj7tu$dre$1terabinaries.xmission.com>...
    > Intereting...
    >
    > Does it work in IDS 9.3 too?
    Yes, I have used it on 9.3 and 9.4.
    > I think the same could be achieved by creating a temporary table:
    >
    > select col1 as a, col2 as b from mytable into temp tx with no log;
    > select a,b from tx;
    Yes, the "table(multiset(" will usually (or maybe even always?) result
    in a temp table.
    > Questions:
    >
    > - Wich is better?
    Probably a matter of taste.
    > - Why I may want to use a FROM like that?
    In the example in the previous post there is no reason. But if you
    want to join sets of aggregated data, it is very useful, or filter
    down the results before joining. Don't use it if you don't have to.
    Kristofer Andersson Guest

  6. #6

    Default Re: SQL construct not supported in informix ?


    vk02720 <vk02720my-deja.com> wrote in message
    news:4d814faa.0307180627.1487aadbposting.google.c om...
    > "Art S. Kagel" <kagelbloomberg.net> wrote in message
    news:<pan.2003.07.17.18.01.18.408954.15473bloombe rg.net>...
    > > On Thu, 17 Jul 2003 11:40:27 -0400, vk02720 wrote:
    > >
    > > > The following SQL construct does not work in Informix 7.3 select a,b
    > > > from
    > > > (select col1 a, col2 b from mytable)
    > > >
    > > > This works in Oracle.
    > > > Is this part of SQL standard or an Oracle extension ?
    > > >
    > > > TIA
    > >
    > > Non-standard to the level of SQL that Informix supports.
    > >
    > >
    > > Art S. Kagel
    >
    > Thanks.
    > So what level of SQL standard does Informix support ? And again, is
    > this construct a SQL standard ( as compared to purely an Oracle
    > extension ) ?
    It's in SQL92, so not an Oracle extentsion (unlike that 'horrible' connect
    by :)

    In version 7.3, you'll have to select the derived table into a temp table
    first and then use that in your query.

    (version 8 (XPS) supports it directly - I belive, never used it, and version
    9 has been covered by Kristofer)

    It /ought/ to be supported, since the result of any select is just another
    relvar, but there you go ...
    > Any good website for SQL standard ?
    Tried google ? Google is your friend

    --
    RH



    Richard Harnden Guest

  7. #7

    Default Re: SQL construct not supported in informix ?

    I don't think this is standard. To teh best of my knowledge teh standard
    requires a correlation name for the nested fullselct (or "inline view" in
    Oracle speak).
    Here is what I get in e.g. DB2:
    db2 => create table t1(c1 int);
    DB20000I The SQL command completed successfully.
    db2 => select * from (select * from t1);
    SQL0104N An unexpected token "" was found following ")". Expected tokens
    may
    include: "AS". SQLSTATE=42601
    db2 => select * from (select * from t1) as f;

    C1
    -----------

    0 record(s) selected.

    I don't have IDS installed yet (moving laptops), so I can't test there.

    Cheers
    Serge


    Serge Rielau Guest

Similar Threads

  1. Replies: 5
    Last Post: April 1st, 12:00 AM
  2. tell me the best way to re-construct this menu!
    By jeffdoe webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 1
    Last Post: February 6th, 01:38 AM
  3. Replies: 0
    Last Post: October 1st, 06:15 PM
  4. Difference between Informix connect and Informix Client SDK
    By Francisco Roldan in forum Informix
    Replies: 3
    Last Post: July 9th, 03:00 AM
  5. Replies: 1
    Last Post: June 27th, 05:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139