1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

What's wrong with my sql query

Discussion in 'SQL PL/SQL' started by mukulverma2408, Dec 29, 2015.

  1. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Hi Experts,

    I am trying to write a query to display information about employees who have one or more coworker in their departments with later hire dates but higher salaries.

    I came up with below SQL :
    Code (Text):

    select outer.last_name,outer.salary,inner.salary
    from emp outer
    where exists (select 'X'
                  from emp inner
                  where outer.department_id=inner.department_id
                  and inner.hire_date > outer.hire_date
                  and inner.salary > outer.salary);
     
    which is resulting in below error :

    ERROR at line 1:
    ORA-00904: "INNER"."SALARY": invalid identifier

    What am I missing??
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Is your emp table have salary column?

    I guess it is sal not salary.
     
  3. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Yes it does have, both outer and inner query is alias of same table i.e "emp"

    Below query is working just fine :
    Code (Text):

    select outer.last_name,outer.salary
    from emp outer
    where exists (select 'X'
                  from emp inner
                  where outer.department_id=inner.department_id
                  and inner.hire_date > outer.hire_date
                  and inner.salary > outer.salary);
     
    Seems like there is a problem when i am trying to access salary column of inner emp table.
     
  4. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi,

    The below query without 'inner.salary' works../

    Code (SQL):


    SELECT OUTER.last_name,OUTER.salary
    FROM emp OUTER
    WHERE EXISTS (SELECT 'X'
                  FROM emp INNER
                  WHERE OUTER.department_id=INNER.department_id
                  AND INNER.hire_date > OUTER.hire_date
                  AND INNER.salary > OUTER.salary);

     
    And so, it is not possible to take records from the query in 'exists' condition.,
     
    mukulverma2408 likes this.
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    select outer.last_name,outer.salary,inner.salary
    from emp outer

    Sorry, I didn't observe it.

    Here in select you have used inner.salary. But in from clause you don't have table inner.
    That's why error came. As suggested by Vicky, remove that the it will work.
     
    mukulverma2408 likes this.
  6. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Thank you Guys.

    Just one more thing.

    Is this rule confined to query with "Exist" condition only or it's SQL standard.
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    It's SQL Standard.