0

I am trying to select names of employees who were born in 1985.

I am doing this but it is giving me an error.

SQL> select fname, lname, count(E#) as "total"
  2  extract(year from date, dob) as "year"
  3  from employee
  4  where "year"=1985;
extract(year from date, dob) as "year"
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected 


SQL> select fname, lname, count(E#) as "total"
  2  from employee
  3  where extract(year from date, dob) =1985
  4  group by fname, lname;
where extract(year from date, dob) =1985
                        *
ERROR at line 3:
ORA-00936: missing expression 
  • 1
    You can only extract from one column: extract(year from dob). For details see the manual: docs.oracle.com/cd/E11882_01/server.112/e41084/… – a_horse_with_no_name Apr 22 '15 at 10:15
  • 1
    You also miss a comma after the as "total" in the 1st query - that's why it complaints about a missing FROM. – ypercubeᵀᴹ Apr 22 '15 at 10:21
  • @DBAstudent, Also miss in 1st query in where caluse .. like year ='1985' ; – Md Haidar Ali Khan Apr 22 '15 at 11:11
  • @MdHaidarAliKhan OP has not mentioned the data type of year column. Did he? Perhaps he is storing year as NUMBER. So, why do you think he should put the single-quotation marks at all? Though it is a bad design. – Lalit Kumar B Apr 22 '15 at 11:24
  • I have fname, lname, dob as attributes and i need to get all names with 1985 dob – DBAStudent Apr 22 '15 at 11:24
3
  1. You have a missing comma after the alias "Total" in your first query.
  2. Extract will take single column as input, while you are giving two columns.

You could do it like:

SQL> SELECT COUNT(deptno)          AS "total",
  2    extract(YEAR FROM hiredate) AS "year"
  3  FROM emp
  4  GROUP BY extract(YEAR FROM hiredate);

     total       year
---------- ----------
         2       1982
         1       1983
         1       1980
        10       1981

SQL>

UPDATE

Using the WHERE clause:

SQL> SELECT COUNT(deptno)          AS "total",
  2    extract(YEAR FROM hiredate) AS "year"
  3  FROM emp
  4  WHERE EXTRACT(YEAR FROM hiredate) = 1981
  5  GROUP BY extract(YEAR FROM hiredate);

     total       year
---------- ----------
        10       1981

SQL>
  • why not use "Where" Clause. Because In Oracle The WHERE clause is used to filter the results from a SELECT, INSERT, UPDATE, or DELETE statement. – Md Haidar Ali Khan Apr 22 '15 at 11:18
  • @MdHaidarAliKhan It was just an example to demonstrate the simple use of EXTRACT. Has nothing functional relevance. – Lalit Kumar B Apr 22 '15 at 11:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.