Problems with query in SQL*PLUS

Question

I am getting a ORA-00923 (FROM keyword not found where expected) error when i run this query in sql*plus.

SELECT EMPLOYEE_ID, FIRST_NAME||' '||LAST_NAME AS FULLNAME
FROM EMPLOYEES
WHERE (JOB_ID, DEPARTMENT_ID) 
IN (SELECT JOB_ID, DEPARTMENT_ID FROM JOB_HISTORY)
AND DEPARTMENT_ID=80;

I ran that query in sql developer and guess what, it works without any problem, why I'm getting this error message when I try in sql*plus.

Just a query : Why are you using DEPARTMENT_ID in your where clause twice ? If the DEPARTMENT_ID has to be 80 the you can just retrieve the job_id in your inner query ? — heretolearn
– heretolearn, Commented Oct 1, 2013 at 5:41
In SQL Plus Client, You can break up your query to multiple lines to isolate the problem. — heretolearn
– heretolearn, Commented Oct 1, 2013 at 5:50
I know but, i was just practice the subquery topic. I don't have the same database the book has so i make that query to practice that. xDDDDD — David Acosta
– David Acosta, Commented Oct 16, 2013 at 22:34

Mark · Accepted Answer · 2013-10-01 05:53:44Z

1

SELECT   EMPLOYEE_ID, FIRST_NAME || ' ' || LAST_NAME AS FULLNAME
  FROM   EMPLOYEES
 WHERE   JOB_ID IN (SELECT   JOB_ID
                      FROM   JOB_HISTORY
                     WHERE   DEPARTMENT_ID = 80);

OR

SELECT   EMPLOYEE_ID, FIRST_NAME || ' ' || LAST_NAME AS FULLNAME
  FROM   EMPLOYEES
 WHERE   JOB_ID IN (SELECT   JOB_ID FROM JOB_HISTORY) AND DEPARTMENT_ID = 80;

OR

SELECT   EMPLOYEE_ID, FIRST_NAME || ' ' || LAST_NAME AS FULLNAME
  FROM   EMPLOYEES E
 WHERE   EXISTS (SELECT   NULL
                   FROM   JOB_HISTORY J
                  WHERE   J.JOB_ID = E.JOB_ID)
         AND DEPARTMENT_ID = 80;

edited Oct 1, 2013 at 5:53

Mark

8,47115 gold badges52 silver badges81 bronze badges

answered Oct 1, 2013 at 5:51

Aditya Kakirde

5,3051 gold badge15 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mark Over a year ago

This is way better than my answer. +1

Kirill Leontev · Accepted Answer · 2013-10-01 06:09:02Z

1

Your query is totally valid and runs in sqlplus exactly as it should:

14:04:01 (41)HR@sandbox> l
  1  SELECT EMPLOYEE_ID, FIRST_NAME||' '||LAST_NAME AS FULLNAME
  2  FROM EMPLOYEES
  3  WHERE (JOB_ID, DEPARTMENT_ID)
  4  IN (SELECT JOB_ID, DEPARTMENT_ID FROM JOB_HISTORY)
  5* AND DEPARTMENT_ID=80
14:04:05 (41)HR@sandbox> /

34 rows selected.

Elapsed: 00:00:00.01

You encounter ORA-00923 only when you have a syntax error. Like this:

14:04:06 (41)HR@sandbox> ed
Wrote file S:\spool\sandbox\BUF_HR_41.sql

  1  SELECT EMPLOYEE_ID, FIRST_NAME||' '||LAST_NAME AS FULLNAME X
  2  FROM EMPLOYEES
  3  WHERE (JOB_ID, DEPARTMENT_ID)
  4  IN (SELECT JOB_ID, DEPARTMENT_ID FROM JOB_HISTORY)
  5* AND DEPARTMENT_ID=80
14:05:17 (41)HR@sandbox> /
SELECT EMPLOYEE_ID, FIRST_NAME||' '||LAST_NAME AS FULLNAME X
                                                           *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

Probably you made one while copying your query from sqldeveloper to sqlplus? Are you sure that your post contains exactly, symbol-to-symbol, the query you're actually trying to execute? I would pay more attention to query text and error message - it usually points at an error, like * under X in my example.

answered Oct 1, 2013 at 6:09

Kirill Leontev

11k8 gold badges46 silver badges50 bronze badges

1 Comment

David Acosta Over a year ago

Thanks for the anawer. maybe it was a syntax error i didn't see, i ran the query again and it worked.

Mark · Accepted Answer · 2013-10-01 05:48:40Z

0

I don't know what you are trying to achieve but here's a possible solution:

/* Formatted on 10/1/2013 1:50:20 PM (QP5 v5.126.903.23003) */
SELECT   EMPLOYEE_ID, FIRST_NAME || ' ' || LAST_NAME AS FULLNAME
  FROM      EMPLOYEES EMP
         JOIN
            JOB_HISTORY JH
         ON EMP.JOB_ID = JH.JOB_ID AND EMP.DEPARTMENT_ID = JH.DEPARTMENT_ID
 WHERE   EMP.DEPARTMENT_ID = 80;

answered Oct 1, 2013 at 5:48

Mark

8,47115 gold badges52 silver badges81 bronze badges

3 Comments

Kirill Leontev Over a year ago

"I don't know what you are trying to achieve but here's a possible solution" made me smile. no offence, sorry )

Mark Over a year ago

@beherenow, the question itself really lack explanation of what he really tries to achieve. Sorry.=D

David Acosta Over a year ago

Just practice with subqueries =)

Collectives™ on Stack Overflow

Problems with query in SQL*PLUS

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related