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!

ERROR at line 1: ORA-00923: FROM keyword not found where expected

Discussion in 'SQL PL/SQL' started by raidermegaman, Nov 1, 2011.

  1. raidermegaman

    raidermegaman Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I'm trying to list the length of tenure for each teacher.

    SELECT *, SYSDATE- STARTDATE AS TENURE
    FROM pulsiferc1_teacher;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
    SQL> CREATE TABLE pulsiferc1_teacher(
      2          id      NUMBER,
      3          name    varchar2(40),
      4          startdate DATE,
      5          subject varchar2(40));
     
    TABLE created.
     
    SQL>
    SQL> BEGIN
      2          FOR i IN 1..10 loop
      3          INSERT INTO pulsiferc1_teacher
      4          (id, name, startdate, subject)
      5          VALUES
      6          (i, 'Dimples McSweeny', sysdate -(i*1000), 'Home Economics');
      7          END loop;
      8
      9          commit;
     10
     11  END;
     12  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT *, SYSDATE- STARTDATE AS TENURE
      2  FROM pulsiferc1_teacher;
    SELECT *, SYSDATE- STARTDATE AS TENURE
            *
    ERROR at line 1:
    ORA-00923: FROM keyword NOT found WHERE expected

    SQL>
    SQL> SELECT t.*, SYSDATE- t.STARTDATE AS TENURE
      2  FROM pulsiferc1_teacher t;
     
            ID NAME                                     STARTDATE SUBJECT                                      TENURE
    ---------- ---------------------------------------- --------- ---------------------------------------- ----------
             1 Dimples McSweeny                         04-FEB-09 Home Economics                                 1000
             2 Dimples McSweeny                         11-MAY-06 Home Economics                                 2000
             3 Dimples McSweeny                         15-AUG-03 Home Economics                                 3000
             4 Dimples McSweeny                         18-NOV-00 Home Economics                                 4000
             5 Dimples McSweeny                         22-FEB-98 Home Economics                                 5000
             6 Dimples McSweeny                         29-MAY-95 Home Economics                                 6000
             7 Dimples McSweeny                         01-SEP-92 Home Economics                                 7000
             8 Dimples McSweeny                         06-DEC-89 Home Economics                                 8000
             9 Dimples McSweeny                         12-MAR-87 Home Economics                                 9000
            10 Dimples McSweeny                         15-JUN-84 Home Economics                                10000
     
    10 ROWS selected.
     
    SQL>
     
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    hi,

    select e.*,sysdate-e.startdate as tenure from pulsiferc1_teacher e;

    like this you need to use. you are not able to write other columns after * is specified in select statement. You need to use object for the table and then try to write column names.

    Thanks,
    Bharat G.