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 missing select key word

Discussion in 'SQL PL/SQL' started by SANKARIPR, Sep 8, 2012.

  1. SANKARIPR

    SANKARIPR Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    hi

    create table test(i number)
    insert into test with cte as ( select 1 from dual union all select 2 from dual);

    error occur what is the solution
    ORA-00928: missing SELECT keyword

    with regards
    sankar
     
  2. SANKARIPR

    SANKARIPR Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    non supporting coloum alias

    with n(sno,name) as(select s.sno,s.name from test1 s where sno=3 union all select j.sno,j.name from test2 j where s.sno=j.sno) select * from n

    querry not run

    unsupported coloum alias
    what is reason for
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: non supporting coloum alias

    I have never known a 'parameterized' WITH clause to run:

    Code (SQL):
    SQL> WITH n(empnbr) AS (
      2          SELECT *
      3          FROM emp
      4          WHERE empno=empnbr
      5  )
      6  SELECT * FROM n(7754);
    SELECT * FROM n(7754)
                   *
    ERROR at line 6:
    ORA-00933: SQL command NOT properly ended

    SQL>
    SQL> WITH n AS (
      2          SELECT *
      3          FROM emp
      4          WHERE empno=&empnbr
      5  )
      6  SELECT * FROM n;
    Enter VALUE FOR empnbr: 7754
    OLD   4:        WHERE empno=&empnbr
    NEW   4:        WHERE empno=7754
     
    no ROWS selected
     
    SQL>
    Also your WITH clause has no closiing paranthesis.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Obviously not as you can't have a create table statement with that strange form. You can have create table ... as select ... but that is not what you wrote:

    Code (SQL):
    SQL> CREATE TABLE yurpee (
      2          smort NUMBER,
      3          greepoo varchar2(40),
      4          tighorper DATE
      5  );
     
    TABLE created.
     
    SQL>
    SQL> BEGIN
      2          FOR i IN 1..1000 loop
      3                  INSERT INTO yurpee
      4                  VALUES (i, 'Uopno'||i, sysdate+i);
      5          END loop;
      6
      7          commit;
      8
      9  END;
     10  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> CREATE TABLE pretwasoo
      2  AS
      3  WITH q AS (
      4          SELECT * FROM yurpee
      5          WHERE smort < 501
      6  )
      7  SELECT * FROM q;
     
    TABLE created.
     
    SQL>
    SQL> DESC pretwasoo

     Name                                      NULL?    TYPE
     ----------------------------------------- -------- ----------------------------
     SMORT                                              NUMBER
     GREEPOO                                            VARCHAR2(40)
     TIGHORPER                                          DATE
     
    SQL>