This discussion is archived
7 Replies Latest reply: Oct 17, 2012 1:59 AM by User538247 RSS

SDE_ORA_Stage_GLHierarchy_Extract Failed

699562 Newbie
Currently Being Moderated
Hi all,

I am trying to do a full load for subject area " Financial - General Ledger ", but faild at SDE_ORA_Stage_GLHierarchy_Extract.
Looked at the session log in Informatica, found getting error when executing the following SQL statement

ORA-00920: invalid relational operator

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)

I also try the statement against the OLTP database, get the same error.

Can anyone help me out?

Thanks in advance,
Roger
  • 1. Re: SDE_ORA_Stage_GLHierarchy_Extract Failed
    660060 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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,

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points