what's wrong with my statement? (simple)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

what's wrong with my statement? (simple)

Author

Topic

gozu
Starting Member

4 Posts

Posted - 2007-08-03 : 22:22:01

Hello well-intentionned reader,

I'm having trouble figuring out what's wrong with my statement. This is what the table looks like. I want to display employees under the manager with the last name King. my statements are below the table

Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 SALARY                                             NUMBER(8,2)
 MANAGER_ID                                         NUMBER(6)

My statement

SQL> SELECT last_name, salary
FROM employees
WHERE manager_id =
        (SELECT employee_id
        FROM employees
        WHERE EMPLOYEE.LAST_NAME = .King.);  2    3    4    5    6
        WHERE EMPLOYEE.LAST_NAME = .King.)
                                   *
ERROR at line 6:
ORA-00936: missing expression

Thank you for your time.

/gozu

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2007-08-03 : 23:00:25

Well you're asking an Oracle question in a SQL Server forum. So you're probably starting behind the 8 ball here, but I'd put some quotes around your strings.
I.E. LAST_NAME = 'King'

Also, if you had more that one manager with the last name King, your subquery won't work. You should change it to

WHERE manager_id IN (

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-08-04 : 06:23:12

select e1.lastname,e1.salary
from employees e1
inner join employees e2 on e1.manager_id=e2.employee_id
where e2.lastname='King'

--------------------
keeping it simple...

gozu
Starting Member

4 Posts

Posted - 2007-08-04 : 19:49:21

I figured it out before Jen replied. Thanks though. Here is my query:

SELECT last_name, salary
FROM employees
WHERE manager_id =
(SELECT employee_id
FROM employees
WHERE last_name = 'King')
ORDER BY last_name;

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-04 : 21:53:19

That will fail if more than one value s returned by the subquery so you probably should have "in" rather than "=".

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources