7 Replies Latest reply: Oct 17, 2012 3:59 AM by User538247 RSS

    SDE_ORA_Stage_GLHierarchy_Extract Failed

    699562
      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
          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
            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
              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
                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
                  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
                    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
                      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,