Subset Forecasting Error ORA-A-00918: column ambiguously defined (Doc ID 1621864.1)

Last updated on MARCH 08, 2017

Applies to:

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

Goal

Customer has created a Subset Forecasting engine profile
They have added the following in the parameter:

Business modeler

PopulationExtraFilter "WHERE M.ITEM_ID=55"


Steps to do the following:
To create Subset Forecasting Profile:
From Business Modeler
• Parameters=> System Parameters=> Engine
• Click “New”
• Choose profile to base subset on---> None
• Enter profile name--> Name of the profile
• Define parameters table to be used -->init_params table name e.g init_params_250
• Choose Profile Type of “Subset Forecasting”----> Select Parent Batch Profile
• Click OK

To set the PopulationExtraFilter
Enter Business Modeler
• Parameters=> System Parameters=> Engine=> Shell
• Enter value Parameter PopulationExtraFilter = 'where Item_id=55'

When they run the subset forecasting engine with the above configuration, they get the error in the engine logs:

21:04:18:339 1 engines were created.
21:04:19:236 Computer localhost speed = 2.53
21:04:19:953 Machine localhost (ID: 0) receiving Branch: 1
21:04:21:664 21:04:21:664 ERROR 21:04:21:765 21:04:21:765 ERROR DB_Command::Execute threw an exception:
21:04:21:865 21:04:21:865 ERROR SELECT s.ITEM_ID, s.LOCATION_ID, s.sales_date, greatest(nvl(pseudo_sale,nvl(actual_quantity, src_actual_quantity))*(1 + nvl(demand_fact,0)),0) FROM SDE_1 s ,MDP_1 m Where s.ITEM_ID = m.item_id AND s.LOCATION_ID = m.location_id AND m.do_fore = 1 AND (m.prediction_status = '1' AND ITEM_ID=55) AND s.sales_date 21:04:21:965 21:04:21:965 ERROR - ErrorDescription: ORA-00918: column ambiguously defined

21:04:22:066 21:04:22:065 ERROR - ErrorNumber: 0x80040e14 - Undefined Error.
21:04:22:166 21:04:22:166 ERROR - NativeErrorNumber: 918
21:04:22:266 21:04:22:266 ERROR - ErrorDescription: One or more errors occurred during processing of command.
21:04:22:366 21:04:22:366 ERROR - ErrorNumber: 0x80040e14 - Undefined Error.
21:04:22:466 21:04:22:466 ERROR - NativeErrorNumber: 0
21:04:22:566 21:04:22:566 ERROR Parameter 0 : 21:10:2013
21:04:22:666 21:04:22:666 ERROR Failed retrieve actual quantity data, check validity of quantity_form or ProportionSeries parameter (if active)
21:04:22:766 21:04:22:766 ERROR Caught exception in DmM3Output::Initialise()
21:04:24:250 ERROR Engine (ID: 0) on machine localhost failed to execute Branch: 1 successfully. HRESULT=0x80004005 - Unspecified error

If they try to run the above sql manually from command line they get the same error of

ORA-00918: column ambiguously defined
 

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