9 Replies Latest reply: Dec 18, 2012 11:26 AM by 897978 RSS

    Joins with subselect from Source generated incorrectly

    897978
      CLAIM.CLAIM_NUM=INFO.CLAIM_NUM
      and INFO.NAME_TYPE_CODE in ('ATTY','ATTN')
      and substring(CLAIM.CLAIM_NUM,1,2) in ('HO','MH','MP')
      and substring(INFO.CLAIMANT_NUM,9,2) = '01'
      and INFO.UPDATE_DATE = (
      select MAX(UPDATE_DATE)
      from <%=snpRef.getInfo("SRC_CATALOG")%>.<%=snpRef.getInfo("SRC_SCHEMA")%>.D_CLM_NAME_INFO
      where CLAIM_NUM = CLAIM.CLAIM_NUM)

      The <%=snpRef.getInfo("SRC_CATALOG")%> and <%=snpRef.getInfo("SRC_SCHEMA")%> in this join resolves to the staging area catalog and schema, not the source catalog and schema of the source.

      This resolved correctly in Sunopsis.
        • 1. Re: Joins with subselect from Source generated incorrectly
          JeromeFr
          HI,

          Could you give us some context ?

          Which version of ODI? Which technology used?


          If using ODI 10g or ODI 11g, please replace snpRef by odiRef.



          Regards,

          JeromeFr
          • 2. Re: Joins with subselect from Source generated incorrectly
            897978
            Version: ODI 11g.1.1.5

            Technology: MSSQL2008

            replacing snpREF by odiREF has no effect

            One solution would be to copy all the sources into the Staging Area before joining, but these tables are large and the expected output is small.

            Knowledge module: IKM SQL to SQL Append

            Failing Step: 3 Insert New Rows

            Failing Message (paraphrased): The [Source] Schema and Table are not in the Staging Catalog
            • 3. Re: Joins with subselect from Source generated incorrectly
              897978
              I thought the following would work:

              CLMT.CLAIMANT_NUM=D_C.CLAIMANT_NUM
              and D_C.NAME_TYPE_CODE in ('ATTY','ATTN')
              and D_C.UPDATE_DATE = (
              SELECT MAX(UPDATE_DATE)
              FROM <%=odiRef.getCatalogName("ODSHO")%>.<%=odiRef.getSchemaName("ODSHO")%>.D_CLM_NAME_INFO
              WHERE D_C.CLAIMANT_NUM=CLAIMANT_NUM
              AND D_C.NAME_TYPE_CODE IN ('ATTY','ATTN')
              )

              Where ODSHO is the LogicalSchema name, but that throws an "unknown location" error.
              • 4. Re: Joins with subselect from Source generated incorrectly
                897978
                In general, how do you join to the most recent row in a table with a datetime/timestamp column in ODI?
                • 5. Re: Joins with subselect from Source generated incorrectly
                  mRainey
                  It looks like you're trying to join to the source table from the staging schema, correct? Unless you have a database link, or the source schema is on the same database server, then this will not work. Even with a dblink, the performance might not be great.

                  You will need to join to the I$ table in the work schema. http://docs.oracle.com/cd/E14571_01/integrate.1111/e12645/odiref_reference.htm#CIAFGGCG

                  If you need to use the entire source table for the lookup, rather than just what's been brought into the I$ table, you'll need to perform the lookup and joins all against the source.

                  Regards,
                  Michael Rainey
                  • 6. Re: Joins with subselect from Source generated incorrectly
                    JeromeFr
                    Hi,

                    Could you check that the location of your join is set on the source and not on the staging area ?
                    • 7. Re: Joins with subselect from Source generated incorrectly
                      897978
                      The tables being joined are definitely in the Source, not the Staging Area.

                      I could go to the Source and create a view joining the Source tables with the subselect to get the 'current' row, but that doesn't seem very much like ODI/Sunopsis Extract/Load/Transform.
                      • 8. Re: Joins with subselect from Source generated incorrectly
                        JeromeFr
                        What does your flow tab look like ?

                        Do you have your two source tables and a join on the left, then one LKM-link towards your staging (right) ?
                        Or do you have two LKM-links and then a join on the staging area ?

                        If it looks like the second, go back to the mapping tab, click on the join and change the execution location to source.
                        • 9. Re: Joins with subselect from Source generated incorrectly
                          897978
                          The join was executing on the Staging Area. I changed the join to execute on the Source, but the odiRef still resolves to the Staging Area and the interface fails.

                          The Flow tab just shows a LKM SQL to SQL icon nested in the Target Icon.

                          If I change the Flow Tab Staging Area to be different from the Target, the Source Icon moves out of the Target Icon, but the LKM selection is empty.

                          If I select a LKM in the Source Icon the flow screen becomes blank and I see:

                          "Drag objects from the Navigator here to model them. Alternatively use the component palette to create new objects."

                          I can't think of a rational way to understand this message.

                          Anyway, I can't think of an interface application where GetInfo SRC_CATALOG or SCR_SCHEMA should ever/would ever resolve to the Staging Area. The staging area, if it is used, is empty at the time any join I can create in the interface is active.

                          In Knowledge Modules, yes, the staging area is important and all the heavy lifting takes place there, but as an interface developer I don't see/can't use that.

                          Right now my solution is to hard code the Catalog and Schema in the subselect.