5 Replies Latest reply: Mar 27, 2012 10:05 AM by 914719 RSS

    Correct usage of variables

    914719
      Good morning Ladies and Gentlemen,

      I'm working with ODI 11 and Oracle 10g.
      I'm trying to customize an IKM step. I want to replace the <%=odiRef.getFrom(i)%> statement by a variable (vMyTable). The problem is that when I run the interface, the code generated contains the variable's name instead of its value. (see the code below)
      My goal is to be able to use variables at any place of this code. Today is in the from statement but tomorrow it could be in the where and so on.
      May you please provide me your assistance?
      Thank you.

      IKM step code (Technology: Oracle):
      ========================
      <%
      String vMyTable = "TEST_TABLE";
      for (int i=0; i < odiRef.getDataSetCount(); i++){%>
      <%=odiRef.getDataSet(i, "Operator")%>
      select <%=odiRef.getPop("DISTINCT_ROWS")%>
      <%=odiRef.getColList(i,"", "[EXPRESSION]", ",\n\t", "", "(((INS or UPD) and !TRG) and REW)")%>
      from vMyTable
      where (1=1)
      <%=odiRef.getJoin(i)%>
      <%=odiRef.getFilter(i)%>
      <%=odiRef.getJrnFilter(i)%>
      <%=odiRef.getGrpBy(i)%>
      <%=odiRef.getHaving(i)%>
      <%}%>

      Code generated:
      ========================
      select
      col_1,
      col_2
      from vMyTable
      where
      (1=1)

      I'm expecting:
      ========================
      select
      col_1,
      col_2
      from TEST_TABLE
      where
      (1=1)
        • 1. Re: Correct usage of variables
          898139
          I dont understand why you want to declare a variable with in the KM. If you explain what you trying to do and why that would help. If you have that declared as a project variable or global variable you can pull that in the KM if not you can use options as shown below

          Instead of using a variable try using an option. say TABLE_NAME
          you can use either one to get the value
          <%=odiRef.getOption("TABLE_NAME")%>
          or
          <%=odiRef.getUserExit("TABLE_NAME")%>

          That way you can set values as required.

          Thanks
          Rams.

          Edited by: Rams on Mar 21, 2012 9:16 AM
          • 2. Re: Correct usage of variables
            914719
            Hello,

            I want to use custome table names to replace the default table names for I$_, E$_ or C$_.
            I already followed the instructions I found in other sites about modifying the Topology. It didn't work. ODI keeps using the default table names.
            Using option could be ok, but can you assign values to the OPTIONS from a knowledge module?
            So my idea is simple:
            1. Get the custome table name: vMyTable = "I$_" + '<%=odiRef.getSession("SESS_NAME")%>';
            2. Get the default ODi table name: vLoadTable = '<%=odiRef.getTable("L", "COLL_NAME", "W")%>';
            3. Get the default from statement: vSources = '<%=odiRef.getFrom()%>';
            4. Replace the default table name by the custome one : vSources = vSources .replace(vLoadTable, vMyTable);
            4. Use my variable instead of odiRef.getFrom().

            I hope this explanation helps.

            Thank you
            • 3. Re: Correct usage of variables
              Cezar Santos
              Hi,

              try to use:

              <%
              String vMyTable = "TEST_TABLE";
              for (int i=0; i < odiRef.getDataSetCount(); i++){%>
              <%=odiRef.getDataSet(i, "Operator")%>
              select <%=odiRef.getPop("DISTINCT_ROWS")%>
              <%=odiRef.getColList(i,"", "[EXPRESSION]", ",\n\t", "", "(((INS or UPD) and !TRG) and REW)")%>
              from *<%=vMyTable%>*
              where (1=1)
              <%=odiRef.getJoin(i)%>
              <%=odiRef.getFilter(i)%>
              <%=odiRef.getJrnFilter(i)%>
              <%=odiRef.getGrpBy(i)%>
              <%=odiRef.getHaving(i)%>
              <%}%>


              Let me knows if works.


              Cezar Santos
              http://odiexperts.com
              • 4. Re: Correct usage of variables
                GurcanO
                To change the prefix/suffix of the work tables, you may change the work table options in Topology Manager, it works. My dba's don't want a table name with a "$" sign, so I all changed them to "-" instead of "$".

                If table name is not fixed and you need to change by each interface, you need to use option for KM and pass it through a variable.

                For more information about using variabes, and usage of variables in KM's you can check my blog posts.

                http://gurcanorhan.wordpress.com/2010/12/14/odi-variables/
                http://gurcanorhan.wordpress.com/2010/11/13/loading-multiple-files-with-odi/

                Cheers,
                Gurcan.
                • 5. Re: Correct usage of variables
                  914719
                  Hello,

                  I tried with <%=vMyTable%> and it did work because I hardcoded the values. However, when I tried to implement the code using the substitution API, it didn't.

                  It is my code:

                  <%
                  vDefTable = "0" + odiRef.getPop("TABLE_NAME");
                  vCusTable = odiRef.getSession("SESS_NAME");
                  vSources = odiRef.getFrom();
                  vFrom = vSources.replace(vDefTable, vCusTable);
                  for (int i=0; i < odiRef.getDataSetCount(); i++){%>
                  <%=odiRef.getDataSet(i, "Operator")%>
                  select <%=odiRef.getPop("DISTINCT_ROWS")%>
                  <%=odiRef.getColList(i,"", "[EXPRESSION]", ",\n\t", "", "((INS and !TRG) and REW)")%>
                  from <%=vFrom%>
                  where (1=1)
                  <%=odiRef.getJoin(i)%>
                  <%=odiRef.getFilter(i)%>
                  <%=odiRef.getJrnFilter(i)%>
                  <%=odiRef.getGrpBy(i)%>
                  <%=odiRef.getHaving(i)%>
                  <%}%>

                  The values I'm expecting to have stored in my variables are:
                  vDefTable = 0TEMP_DEV
                  vCusTable = STEST
                  vSources = ODI_STAGE_DEV.C$_0TEMP_DEV LEFT OUTER JOIN ODI_STAGE_DEV.PRODUCT_LOOKUP PRODUCT_LOOKUP ON C3_PRODUCTID=PRODUCT_LOOKUP.PRODUCTID
                  vFrom = ODI_STAGE_DEV.I$_STEST LEFT OUTER JOIN ODI_STAGE_DEV.PRODUCT_LOOKUP PRODUCT_LOOKUP ON C3_PRODUCTID=PRODUCT_LOOKUP.PRODUCTID

                  When I ran the interface I got an error, and the code for the from is:
                  vFrom = <?=snpRef.getObjectName("L", "%COL_PRF<?=snpRef.getSession("SESS_NAME")?>", "ODI_STAGE", "", "W")?> LEFT OUTER JOIN <?=snpRef.getObjectName("L", "PRODUCT_LOOKUP", "ODI_STAGE", "", "D")?> PRODUCT_LOOKUP ON C3_PRODUCTID=PRODUCT_LOOKUP.PRODUCTID
                  By default it is
                  vFrom = <?=snpRef.getObjectName("L", "%COL_PRF0TEMP_DEV", "ODI_STAGE", "", "W")?> LEFT OUTER JOIN <?=snpRef.getObjectName("L", "PRODUCT_LOOKUP", "ODI_STAGE", "", "D")?> PRODUCT_LOOKUP ON C3_PRODUCTID=PRODUCT_LOOKUP.PRODUCTID

                  As you can see the string 0TEMP_DEV is replaced by <?=snpRef.getSession("SESS_NAME")?> and not STEST

                  It seems that ODI is not performing the substitution to store the values in the variables but the API code itself.

                  I tried to move the variables to a step before using Jython technology but the variables are not found.

                  I tried changing the technology to Oracle but I the agent cannot resolve the methode getTable("L","COLL_NAME","W").

                  I'm really stuck.

                  Please Help!