ODI 12c Generates Cascading Analytic Functions which Result in ORA-30483 Error when used in more than One Expression

(Doc ID 2411438.1)

Last updated on JUNE 22, 2018

Applies to:

Oracle Data Integrator - Version 12.2.1.3.0 and later
Information in this document applies to any platform.

Symptoms

An Oracle Data Integrator (ODI) user is attempting to create a mapping with two expressions where the first expression creates an attribute with an analytic expression (database function) and the second expression uses this to create another attribute with an analytic database function.

The following error occurs when attempting to execute the mapping:

2018-01-22 06:44:37.487 NOTIFICATION:16 ODI-1126 Agent <NAME> started session MM_TEST_MAPPING_Physical_SESS (2695) in work repository <Workrep> using context GLOBAL. 2018-01-22 06:44:37.829 NOTIFICATION New data source: [DWH_ETL/*******@jdbc:oracle:thin:@<HOSTNAME>:1521/<SID>] 2018-01-22 06:44:37.949 WARNING SQLCommand execution failure: ORA-30483: window functions are not allowed here Failed query text: INSERT /*+ APPEND PARALLEL */ INTO NEO.MM_SR_TEST_TARGET ( ITEM_ID , ONLY_ONE_ENTRY ) SELECT DISTINCT_.ID , DISTINCT_.ONLY_ONE_ENTRY FROM ( SELECT DISTINCT SR_TEST.ID AS ID , (CASE WHEN COUNT(DISTINCT(CASE WHEN (first_value(SR_TEST.ITEM_ORDER_NUM) over(PARTITION BY SR_TEST.ITEM_ID ORDER BY SR_TEST.ITEM_ORDER_NUM DESC)) = SR_TEST.ITEM_ORDER_NUM THEN 1 ELSE 0
  END)) over(PARTITION BY SR_TEST.ITEM_ID) = 1 THEN 1 ELSE 0 END) AS ONLY_ONE_ENTRY FROM NEO.MM_SR_TEST SR_TEST ) DISTINCT_

Changes

 

Cause

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