CODEWITHSUNDEEP
sqloracle-database
user1874311
user1874311

Reputation: 943

SQL Error: ORA-00923: FROM keyword not found where expected

I tried using the LISTAGG function before, but since there is a likelihood that the output would be pretty long, maybe even more than 4000 bytes.. I kept getting the error that the result of string concatenation is too long. I followed AskTom page that Tim references about the implementation of a string aggregation function that returns a CLOB.

           CREATE TABLE FINAL_LOG AS
           SELECT SESSION_DT, C_IP, CS_USER_AGENT,
           concat_all_ot(WEB_LINK, ' ')
           WITHIN GROUP(ORDER BY C_IP, CS_USER_AGENT) AS "WEBLINKS"
               FROM weblog_views
               GROUP BY C_IP, CS_USER_AGENT, SESSION_DT
               ORDER BY SESSION_DT

For this I get the following error,

SQL Error: ORA-00923: FROM keyword not found where expected

Can anyone tell me where I'm going wrong with this?

Upvotes: 0

Views: 5383

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

If you're using a user-defined aggregate function, you probably need to omit the WITHIN GROUP clause. It also doesn't make sense to specify an ORDER BY clause in a CREATE TABLE statement since the data in a heap-organized table is inherently unordered. Also, if you are using the code in that link, the name of the aggregate function is concat_all. concat_all_ot is the object type. So you'd want something like

CREATE TABLE FINAL_LOG  AS   
  SELECT SESSION_DT, 
         C_IP, 
         CS_USER_AGENT,
         concat_all( concat_expr(WEB_LINK, ' ') ) AS "WEBLINKS"
    FROM weblog_views    
   GROUP BY C_IP, 
            CS_USER_AGENT, 
            SESSION_DT

Upvotes: 5

Related Questions