4 Replies Latest reply on Nov 18, 2010 2:58 PM by 809303

    Using journalized data in an interface with aggragate function

    809303
      Hi

      I am trying to use the journalized data of a source table in one of my interfaces in ODI. The trouble is that one of the mappings on the target columns involves a aggregate function(sum). When I run the interface i get an error saying "not a group by expression". I checked the code and found that the jrn_subscriber, jrn_flag and jrn_date columns are included in the select statement but not in the group by statement(the group by statement only contains the remiaining two columns of the target table).

      Is there a way around this? Do I have to manually modify the km? If so how would I go about doing it?

      Also I am using Oracle GoldenGate JKM (oracle to oracle OGG).

      Thanks and really aprreciate the help

      Ajay
        • 1. Re: Using journalized data in an interface with aggragate function
          793879
          Go to support.oracle.com
          and search for this ID 424344.1. It will provide you with all the details.

          Thanks,
          Sachin
          • 2. Re: Using journalized data in an interface with aggragate function
            809303
            Hi Sachin

            Thanks for that. But where exactly in support.oracle.com do i search for that ID? I tried searching in knowledge base but no luck. Sorry if this is stupid question, I am new to oracle support.

            Thanks
            Ajay
            • 3. Re: Using journalized data in an interface with aggragate function
              793879
              'ORA-00979' When Using The ODI CDC (Journalization) Feature With Knowledge Modules Including SQL Aggregate Functions [ID 424344.1]
                   Modified 11-MAR-2009 Type PROBLEM Status MODERATED      

              In this Document
              Symptoms
              Cause
              Solution
              Alternatives :

              This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

              Applies to:
              Oracle Data Integrator - Version: 3.2.03.01
              This problem can occur on any platform.
              Symptoms
              After having successfully tested an ODI Integration Interface using an aggregate function such as MIN, MAX, SUM, it is necessary to set up Changed Data Capture operations by using Journalized tables.

              However, during execution of the Integration Interface to retrieve only the Journalized records, problems arise at the Load Data step of the Loading Knowledge Module and the following message is displayed in ODI Log:

              ORA-00979: not a GROUP BY expression
              Cause
              Using both CDC - Journalization and aggregate functions gives rise to complex issues.
              Solution

              Technically there is a work around for this problem (see below).
              WARNING : Oracle engineers issue a severe warning that such a type of set up may give results that are not what may be expected. This is related to the way in which ODI Journalization is implemented as specific Journalization tables. In this case, the aggregate function will only operate on the subset which is stored (referenced) in the Journalization table and NOT over the entire Source table.

              We recommend to avoid such types of Integration Interface set ups.
              Alternatives :

              1.The problem is due to the missing JRN_* columns in the generated SQL "Group By" clause.

              The work around is to duplicate the Loading Knowledge Module (LKM), and in the clone, alter the "Load Data" step by editing the "Command on Source" tab and by replacing the following instruction:
              <%=odiRef.getGrpBy()%>

              with
              <%=odiRef.getGrpBy()%>
              <%if ((odiRef.getGrpBy().length() > 0) && (odiRef.getPop("HAS_JRN").equals("1"))) {%>
              ,JRN_FLAG,JRN_SUBSCRIBER,JRN_DATE
              <%}%>

              2. It is possible to develop two alternative solutions:

              (a) Develop two separate and distinct Integration Interfaces:

              * The first Integration Interface loads data into a temporary Table and specify the aggregate functions to be used in this initial Integration Interface.
              * The second Integration Interfaces uses the temporary Table as a Source. Note that if you create the Table in the Interface, it is necessary to drag and drop the Integration Interface into the Source panel.

              (b) Define two connections to the Database so that the Integration Interface references two distinct and separate Data Server Sources (one for the Journal, one for the other Tables). In this case, the aggregate function will be executed on the Source Schema.




              Show Related Information Related
              Products

              * Middleware > Business Intelligence > Oracle Data Integrator (ODI) > Oracle Data Integrator

              Keywords
              ODI; AGGREGATE; ORACLE DATA INTEGRATOR; KNOWLEDGE MODULES; CDC; SUNOPSIS
              Errors
              ORA-979


              Please find above the content from OTN.
              It should show you this if you search this ID in the Search Knowledge Base

              Cheers
              Sachin
              • 4. Re: Using journalized data in an interface with aggragate function
                809303
                Thats great, that helped me a lot

                Thanks a lot Sachin.