ORA-00904: Demantra Worksheet Error

(Doc ID 1535772.1)

Last updated on JANUARY 03, 2017

Applies to:

Oracle Demantra Demand Management - Version 7.3.1 and later
Information in this document applies to any platform.

Symptoms

Customer is on 731. They have created a series on the table sales_data. This series has a  Server Expression which contains "Extra From" and "Extra Where" specified on the view S_DATA.

 

S_DATA is a view and should have the equal number of records as in sales_data table. S_DATA is used only for having some aggregated values to be shown in the series. It has only 4 columns (sales_date, location_id, item_id and m_arctic_fox).

======================================
SQL> select count(*) from sales_data;

COUNT(*)
463902

SQL> select count(*) from s_data;

COUNT(*)
463902
======================================

Customer also mentions that they have proportion on the same series.

 

 

But when they run the worksheet they get the following error:

ODPM-10303 : The application could not process your update request- encountered configuration inconsistencies. Please contact your system administrator. More information is available in application log files.

The collaborator logs show the following:

2012-11-28 15:21:20,205 CST Update_1 ERROR update.sql: ODPM-70173: Error SQL:

CREATE TABLE T_UPDATE_510_1354137542448 NOLOGGING CACHE AS
SELECT SALES_DATA.ITEM_ID
,SALES_DATA.LOCATION_ID
,SALES_DATA.SALES_DATE
FROM SALES_DATA
,MDP_MATRIX
WHERE MDP_MATRIX.T_EP_P2A_EP_ID IN (256)
AND MDP_MATRIX.T_EP_MOTO_KEY_ACC_DESC_EP_ID IN (21)
AND SALES_DATA.ITEM_ID = S_DATA.ITEM_ID
AND SALES_DATA.LOCATION_ID = S_DATA.LOCATION_ID
AND SALES_DATA.SALES_DATE = S_DATA.SALES_DATE
AND MDP_MATRIX.T_EP_P2A_EP_ID = 256
AND ((SALES_DATA.SALES_DATE >= TO_DATE('11-18-2012','mm-dd-yyyy')
AND SALES_DATA.SALES_DATE AND SALES_DATA.ITEM_ID = MDP_MATRIX.ITEM_ID
AND SALES_DATA.LOCATION_ID = MDP_MATRIX.LOCATION_ID
GROUP BY SALES_DATA.ITEM_ID,SALES_DATA.LOCATION_ID,SALES_DATA.SALES_DATE
2012-11-28 15:21:20,207 CST Update_1 ERROR update.sql: ODPM-70125: Failed to create temp update table. Update Data: Id: 1354137680065, User:
q13616, WS: 15985, Update Table: SALES_DATA, Combination: L11M256 SELECT
SALES_DATA.ITEM_ID
,SALES_DATA.LOCATION_ID
,SALES_DATA.SALES_DATE
FROM SALES_DATA
,MDP_MATRIX
WHERE MDP_MATRIX.T_EP_P2A_EP_ID IN (256)
AND
MDP_MATRIX.T_EP_MOTO_KEY_ACC_DESC_EP_ID IN (21)
AND SALES_DATA.ITEM_ID = S_DATA.ITEM_ID
AND SALES_DATA.LOCATION_ID =
S_DATA.LOCATION_ID
AND SALES_DATA.SALES_DATE = S_DATA.SALES_DATE
AND MDP_MATRIX.T_EP_P2A_EP_ID =
256
AND ((SALES_DATA.SALES_DATE >= TO_DATE('11-18-2012','mm-dd-yyyy')
AND
SALES_DATA.SALES_DATE AND SALES_DATA.ITEM_ID =
MDP_MATRIX.ITEM_ID
AND SALES_DATA.LOCATION_ID = MDP_MATRIX.LOCATION_ID
GROUP BY
SALES_DATA.ITEM_ID,SALES_DATA.LOCATION_ID,SALES_DATA.SALES_DATE
2012-11-28 15:21:20,209 CST Update_1 ERROR update.sql:
com.demantra.applicationServer.appServerExceptions.ApplicationServerException: com.demantra.applicationServer.appServerExceptions.FailedToExcuteBatch: java.sql.BatchUpdateException: error occurred during batching: ORA-00904: "S_DATA"."SALES_DATE": invalid identifier

at com.demantra.applicationServer.services.DBServicesCommon.populateTableAs(DBServicesCommon.java:2020)
at com.demantra.applicationServer.services.DBServices.populateTableAs(DBServices.java:1148)
at com.demantra.applicationServer.services.tempTable.TempTable.setTable(TempTable.java:449)
at com.demantra.applicationServer.nupdate.process.TempUpdateTableHandler.createTempUpdateTable(TempUpdateTableHandler.java:145)
at com.demantra.applicationServer.nupdate.process.UpdateCombTask.createTempUpdateTable(UpdateCombTask.java:321)
at com.demantra.applicationServer.nupdate.process.UpdateCombTask.execute(UpdateCombTask.java:80)
at com.demantra.applicationServer.nupdate.process.UpdateCombSDTask.execute(UpdateCombSDTask.java:70)
at com.demantra.common.queue.QueueTask.run(QueueTask.java:55)
at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:743)
at java.lang.Thread.run(Thread.java:595)
Caused by: com.demantra.applicationServer.appServerExceptions.FailedToExcuteBatch: java.sql.BatchUpdateException: error occurred during batching: ORA-00904: "S_DATA"."SALES_DATE": invalid identifier

at com.demantra.applicationServer.services.DBServicesCommon.excuteBatch(DBServicesCommon.java:1313)
at com.demantra.applicationServer.services.DBServicesCommon.populateTableAs(DBServicesCommon.java:2011)
... 9 more
Caused by: java.sql.BatchUpdateException: error occurred during batching: ORA-00904: "S_DATA"."SALES_DATE": invalid identifier

at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:457)
at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:502)
at oracle.jdbc.driver.OracleStatement.executeBatch(OracleStatement.java:3816)


The view S_DATA does have a column sales_date


It seems it considered the conditions they specified in "Extra Where" but it didn't consider the "Extra From" that they specified which causes this issue.



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