ODI Jobs Failing With ORA-00923 error (Doc ID 2155960.1)

Last updated on JULY 13, 2016

Applies to:

Oracle Knowledge - Version 8.6 and later
Information in this document applies to any platform.

Goal

On : 8.6 version, Analytics Reporting and ETL

ODI jobs failing with the following Oracle error:

ORA-00923 error msg "from keyword not found where expected"

The specific query that is failing is:
CREATE TABLE dw_reporting.TEMP_BRIDGE_QUESTN_RESPONSE
as
  (
  SELECT max(data_id) as data_id , questn_key
  FROM (
  SELECT data_id, questn_key, response_key, brg_num_in_set,
ind_upd,
  row_number() OVER (PARTITION BY questn_key ,
brg_num_in_set ORDER BY data_id) AS rownumber
  FROM (
  select data_id, questn_key,
brg_num_in_set,ind_upd,output as response_key
  from
  (select data_id, questn_key,
brg_num_in_set,ind_upd, listagg(response_key,',') WITHIN GROUP (ORDER BY
data_id ) over (partition by data_id ) as output,
  count(response_key) over (partition by
data_id order by response_key) as running_count,
  count(response_key) over (partition by
data_id) as tot_count
  from (SELECT * FROM
dw_reporting.bridge_questn_response_stg ORDER BY questn_key, response_key))
  where running_count = tot_count
  )
  
  )
  group by questn_key,response_key
  )
 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms