This content has been marked as final.
Show 7 replies

1. Re: SDE_ORA_Stage_GLHierarchy_Extract Failed
660060 Oct 27, 2009 10:26 AM (in response to 699562)Please check the DB versions of both source/target and make sure they meet your BIAPPS system requirement guide. If your OLTP is in 9i versions then some of the connect by functions may not work, they will work only in 10G onwards. In that case, you may have to customize your mappings.
Please check
Bug 8981172: SDE_ORA_STAGE_GL_HIERARCHY_EXTRACT FAILING WITH INVALID RELATIONAL OPERATOR
rm
Edited by: user737238 on Oct 27, 2009 8:24 AM 
2. Re: SDE_ORA_Stage_GLHierarchy_Extract Failed
676254 Oct 27, 2009 12:52 PM (in response to 699562)The problem is with the last part of the query
i.e
AND ( rg_report_calculations.axis_seqrg_report_calculations.axis_seq_low
OR rg_report_calculations.axis_seqrg_report_calculations.axis_seq_high
)
There is no condition , i mean it would end up looking like AND (5 0r 25)
which is not the right way of relational operator .. it should rather be like this
rg_report_calculations.axis_seq
BETWEEN rg_report_calculations.axis_seqrg_report_calculations.axis_seq_low AND rg_report_calculations.axis_seqrg_report_calculations.axis_seq_high
Hope it helps !! 
3. Re: SDE_ORA_Stage_GLHierarchy_Extract Failed
699562 Oct 27, 2009 9:22 PM (in response to 699562)Hi, Thanks your advice.
I also found something missing in the SQL statement. It supposed to be like this
SELECT
RG_REPORT_CALCULATIONS.LAST_UPDATE_DATE,
RG_REPORT_CALCULATIONS.LAST_UPDATED_BY,
RG_REPORT_CALCULATIONS.CREATION_DATE,
RG_REPORT_CALCULATIONS.CREATED_BY,
ROWNUM ROW_NUM,
RG_REPORT_CALCULATIONS.AXIS_SET_ID,
RG_REPORT_CALCULATIONS.AXIS_SEQ,
LEVEL LEVEL_NUM,
RG_REPORT_CALCULATIONS.AXIS_SEQ_LOW,
RG_REPORT_CALCULATIONS.AXIS_SEQ_HIGH,
RG_REPORT_AXIS_SETS.STRUCTURE_ID CHART_OF_ACCOUNTS
FROM
RG_REPORT_CALCULATIONS,
RG_REPORT_AXIS_SETS
WHERE RG_REPORT_CALCULATIONS.APPLICATION_ID='101' AND
RG_REPORT_AXIS_SETS.AXIS_SET_ID = RG_REPORT_CALCULATIONS.AXIS_SET_ID
CONNECT BY NOCYCLE PRIOR RG_REPORT_CALCULATIONS.AXIS_SET_ID = RG_REPORT_CALCULATIONS.AXIS_SET_ID
AND PRIOR RG_REPORT_CALCULATIONS.AXIS_SEQ BETWEEN RG_REPORT_CALCULATIONS.AXIS_SEQ_LOW AND RG_REPORT_CALCULATIONS.AXIS_SEQ_HIGH
AND (RG_REPORT_CALCULATIONS.AXIS_SEQ<> RG_REPORT_CALCULATIONS.AXIS_SEQ_LOW OR RG_REPORT_CALCULATIONS.AXIS_SEQ<> RG_REPORT_CALCULATIONS.AXIS_SEQ_HIGH )
There two columns the transformation gets are empty AXIS_SEQ_LOW and AXIS_SEQ_HIGH.
But even running the SQL satatement against the OLTP database which the version is 9i still get the same error.
I am not using FSG to setup Account Hierarchy, So the value of parameter "" is 'N'.
If the SQL statement modification is needed. How do I know what the correct one is?
Thanks a lot,
Roger 
4. Re: SDE_ORA_Stage_GLHierarchy_Extract Failed
676254 Oct 28, 2009 9:56 AM (in response to 699562)I ran the query by commenting out the last part and it ran successfully .
the query needs to come from Oracle Support so i guess you will have to chase them down for a quick fix.
You can also check if the queryfor this job is same in other adapters like 11.5.9, 11.5.10 , R12 etc ..may or may not help but just an idea !!
Cheers,
Sid 
5. Re: SDE_ORA_Stage_GLHierarchy_Extract Failed
676254 Oct 28, 2009 10:40 AM (in response to 676254)What i meant is do you have a Imperosnate user defined in the repo ?
Do you have any kind of web server log for Ichain which could show us if the authentication is failing over there ...
Currently how is the authentication block setup in the repo ?? 
6. Re: SDE_ORA_Stage_GLHierarchy_Extract Failed
699562 Oct 29, 2009 10:04 PM (in response to 676254)Run the script against differenct database 9.2.0.6.0 , get error Invalid relational Operator.
Run the script against differenct database 10.2.0.3.0, no error occur.
So it seems a bug for ORA_11_5_10_Adapter in Informatica.
Roger 
7. Re: SDE_ORA_Stage_GLHierarchy_Extract Failed
User538247 Oct 17, 2012 3:59 AM (in response to 699562)hi,
it should be as
SELECT RG_REPORT_CALCULATIONS.LAST_UPDATE_DATE,
RG_REPORT_CALCULATIONS.LAST_UPDATED_BY,
RG_REPORT_CALCULATIONS.CREATION_DATE,
RG_REPORT_CALCULATIONS.CREATED_BY,
ROWNUM ROW_NUM,
RG_REPORT_CALCULATIONS.AXIS_SET_ID,
RG_REPORT_CALCULATIONS.AXIS_SEQ,
LEVEL LEVEL_NUM,
RG_REPORT_CALCULATIONS.AXIS_SEQ_LOW,
RG_REPORT_CALCULATIONS.AXIS_SEQ_HIGH,
RG_REPORT_AXIS_SETS.STRUCTURE_ID CHART_OF_ACCOUNTS
FROM RG_REPORT_CALCULATIONS,
RG_REPORT_AXIS_SETS
WHERE RG_REPORT_CALCULATIONS.APPLICATION_ID ='101'
AND RG_REPORT_AXIS_SETS.AXIS_SET_ID = RG_REPORT_CALCULATIONS.AXIS_SET_ID
CONNECT BY NOCYCLE PRIOR RG_REPORT_CALCULATIONS.AXIS_SET_ID = RG_REPORT_CALCULATIONS.AXIS_SET_ID
AND PRIOR RG_REPORT_CALCULATIONS.AXIS_SEQ BETWEEN RG_REPORT_CALCULATIONS.AXIS_SEQ_LOW AND RG_REPORT_CALCULATIONS.AXIS_SEQ_HIGH
AND (rg_report_calculations.axis_seq BETWEEN rg_report_calculations.axis_seq_low AND rg_report_calculations.axis_seq_high )
Cheers,