Professional Web Applications Themes

mistake, error, or something else ? - Oracle Server

Hi people. I've this query: select a,b,c from t1 where c in (select distinct c from t2); this query run succesfully BUT: select distinct c from t2 give me: ora-904 Invalid column name. WHY ???? i've no synonym or other objects t1 has 3 columns, a,b,c t2 has 2 columns d,e Tia Mike...

  1. #1

    Default mistake, error, or something else ?

    Hi people.
    I've this query:

    select a,b,c from t1
    where c in (select distinct c from t2);


    this query run succesfully BUT:

    select distinct c from t2
    give me: ora-904 Invalid column name.

    WHY ????

    i've no synonym or other objects

    t1 has 3 columns, a,b,c
    t2 has 2 columns d,e

    Tia

    Mike
    mbrugo@despammed.com Guest

  2. #2

    Default Re: mistake, error, or something else ?

    com wrote:
     
    Which part of "t2 has 2 columns d,e" would lead you to believe that
    "select distinct c from t2" would be successful?

    --
    Daniel Morgan
    http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
    http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
    damorganx.washington.edu
    (replace 'x' with a 'u' to reply)

    Daniel Guest

  3. #3

    Default Re: mistake, error, or something else ?



    Daniel Morgan wrote: 
    > Which part of "t2 has 2 columns d,e" would lead you to believe that
    > "select distinct c from t2" would be successful?
    >
    > --
    > Daniel Morgan[/ref]


    But it is 'sucessful'. Well it won't retrieve anything, but it's valid
    syntax.

    (select distinct c from t2)

    in this sub query is the same as

    (select distinct t1.c from t2)

    and that's legal, but fairly useless code.
    Rob Guest

  4. #4

    Default Re: mistake, error, or something else ?



    Rob Cowell wrote: 
    > > Which part of "t2 has 2 columns d,e" would lead you to believe that
    > > "select distinct c from t2" would be successful?
    > >
    > > --
    > > Daniel Morgan[/ref]
    >
    > But it is 'sucessful'. Well it won't retrieve anything, but it's valid
    > syntax.
    >
    > (select distinct c from t2)
    >
    > in this sub query is the same as
    >
    > (select distinct t1.c from t2)
    >
    > and that's legal, but fairly useless code.[/ref]


    Actually I think the subquery with return all distinct values for t1.c
    as many times as there are rows in t2.

    I guess it's some weird ANSII compatibility thing?
    Rob Guest

  5. #5

    Default Re: mistake, error, or something else ?

    On Mon, 27 Oct 2003 14:26:26 +0000 (UTC), Rob Cowell
    <com> wrote:
     

    i've understand, but it's not much logical

    can you explain me why pr consider the column named in subquery
    like a column owned by t1 and not by t2 ?

    Tia

    Mike
    Mike Guest

  6. #6

    Default Re: mistake, error, or something else ?



    Mike wrote: 
    >
    > i've understand, but it's not much logical
    >
    > can you explain me why pr consider the column named in subquery
    > like a column owned by t1 and not by t2 ?
    >
    > Tia
    >
    > Mike[/ref]

    Because a sub query can reference columns from tables in the main query.
    The pr would consider the column if it existed in t2. But it knows
    column c doesn't exist in t2, so assumes you mean column c in t1 which
    is also in the scope of the sub query.
    Rob Guest

  7. #7

    Default Re: mistake, error, or something else ?

    On Mon, 27 Oct 2003 14:36:42 +0000 (UTC), Rob Cowell
    <com> wrote:

     

    Thanks Rob
    Mike Guest

Similar Threads

  1. Stupid Mistake
    By Applehead in forum Coldfusion Security
    Replies: 1
    Last Post: March 7th, 02:01 PM
  2. was it a mistake AICS on OS X 10.3
    By Jacqui_Ashby@adobeforums.com in forum Adobe Illustrator Macintosh
    Replies: 3
    Last Post: April 29th, 03:35 PM
  3. loadMovie *bug* or my mistake ?
    By phretor in forum Macromedia Flash Actionscript
    Replies: 2
    Last Post: February 10th, 12:13 PM
  4. Beginner Mistake
    By Jesse in forum ASP.NET General
    Replies: 1
    Last Post: July 22nd, 10:54 AM

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