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!

SQL Syntax Error

Discussion in 'SQL PL/SQL' started by Saya22, Dec 4, 2012.

  1. Saya22

    Saya22 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hello,

    I'm working on Oracle SQL Developer: ODMiner. I keep getting a syntax error with the following code,


    CREATE TABLE FINAL_WEBLOG AS
    select SESSION_DT, C_IP, CS_USER_AGENT,
    LISTAGG(WEB_LINK, ' ')
    WITHIN GROUP (ORDER BY C_IP, CS_USER_AGENT) "WEB_LINKS",
    FROM WEBLOG_VIEWS
    GROUP BY C_IP, CS_USER_AGENT, SESSION_DT
    ORDER BY SESSION_DT

    I understand if it's the semicolon that is missing at the end, but I'm not sure on how I can rephrase it correctly.

    The error I got was,

    Error at Command Line:5 Column:2
    Error report:
    SQL Error: ORA-00936: missing expression
    00936. 00000 - "missing expression"
    *Cause:
    *Action:
     
  2. Saya22

    Saya22 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Found my mistake.. :)
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I take it your mistake was splitting the listagg syntax across two lines and the unnecessary comma after "WEB_LINKS":

    Code (SQL):
    SQL> CREATE TABLE weblog_views (session_dt DATE, c_ip varchar2(16), cs_user_agent varchar2(12), web_link varchar2(40));
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE FINAL_WEBLOG AS
    2 SELECT SESSION_DT, C_IP, CS_USER_AGENT,
    3 LISTAGG(WEB_LINK, ' ') WITHIN GROUP (ORDER BY C_IP, CS_USER_AGENT) "WEB_LINKS"
    4 FROM WEBLOG_VIEWS
    5 GROUP BY C_IP, CS_USER_AGENT, SESSION_DT
    6 ORDER BY SESSION_DT;
     
    TABLE created.
     
    SQL>