REPOST: Re: Subselect in the from clause

From: David Fitzjarrell <oratune_at_msn.com>
Date: 25 Jan 2002 06:59:23 -0800
Message-ID: <5$--$%%%%--%__-_-$@news.noc.cabal.int>

John Russell <netnews2_at_johnrussell.mailshell.com> wrote in message news:<cmc25u49ftq51olke46kic8vqjv9af26l2_at_4ax.com>...
>
> Sometimes you might make the subselect return only a single value,
> just so you can get the same value available in every row of the
> result set:
>
> for item in
> (
> select col1, col2, col3, (select max(col1 + col2) from table) col4
> from table
> )
> loop
> -- item.col4 is the same value on every loop iteration
> -- Getting it via subselect saves declaring a separate variable and
> -- doing a SELECT INTO query.
> ...
>
> John

You, of course, actually meant

          select t1.col1, t1.col2, t1.col3, t2.col4
          from table t1, (select max(col1+col2) col4 from table) t2

since a subselect cannot be placed in the column list as you illustrated:

SQL> select col1, col2, col3, (select max(col1 + col2) from table) col4
  2> from table
  3> /
select col1, col2, col3, (select max(col1 + col2) from table) col4
                                                            *

ERROR at line 1:
ORA-00906: missing left parenthesis

• WAS CANCELLED BY =======: From: oratune_at_msn.com (David Fitzjarrell) Control: cancel <32d39fb1.0201250659.c9ff2b2_at_posting.google.com> Subject: cmsg cancel <32d39fb1.0201250659.c9ff2b2_at_posting.google.com> Date: Mon, 28 Jan 2002 01:09:24 GMT Message-ID: <cancel.32d39fb1.0201250659.c9ff2b2_at_posting.google.com> X-No-Archive: yes Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.server NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88 Lines: 1 Path: news.uni-stuttgart.de!news.belwue.de!news.sns-felb.debis.de!bignews.mediaways.net!feedme.news.mediaways.net!news-out.visi.com!hermes.visi.com!news.maxwell.syr.edu!news.stealth.net!msrtrans1!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp04!u&n&a&c&anceller Xref: news.uni-stuttgart.de control:40722988

This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers. Received on Fri Jan 25 2002 - 08:59:23 CST