Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003

    Unanswered: SQL statement (invalid column name)


    I have a question regarding a sql statement. The following statement makes perhaps no sense... it's just to describe my problem.
    Why is the column a.ename not recognized as an valid column?
    I think the problem is, that I try to reference a column from an surrounding select and there are two levels between them. This was not the first time I ran into this problem... so perhaps you know this problem and can give me an hint to solve this ...

    select *
    from emp a,
    (select ename
    from dept,
    (select ename
    from bonus
    where ename = a.ename
    ) b
    where a.ename = b.ename

    Thanks in advance!!!


  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1

    Re: SQL statement (invalid column name)

    The reason is that a and b are "siblings" in the FROM clause of the main query, so neither can reference the other. Or put it another way: b is a an inline view. Imagine replacing the inline view with a real view:

    PHP Code:
    select *
    from emp areal_view b
    where a
    .ename b.ename
    And now try creating real_view:

    PHP Code:
    create view real_view as
    select ename
     from dept
    select ename
      from bonus
      where ename 

    Of course, alias "a" is meaningless in this context!

Posting Permissions

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