top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration

Facebook Login
Site Registration

Dynamic expressions implementation

0 votes
254 views

I have one source table as :

S1       S2       S3       S4
 Hi      There    SSN1     SSN2 

where S1, S2, S3 and S4 are column names.

There is a target table likewise which has three fields:
T1 T2 T3

I have another table which is as follows:

Function            Target_Column
 concat(S1,S2)          T1
 substr(S3,1,3)         T2
 substr(S3,3,1)         T3 

I want to have such a mapping in which it can fetch the function name and map according to the function present in the table to the target column specified. My target table now after running the mapping will be:

T1         T2       T3
 HiThere   SSN       1

How can I achieve this?

posted Aug 5, 2014 by Sachin

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

Looks like you can use the Java API function invokeJExpression() inside Java transformation to derive the target columns.

(datatype)invokeJExpression(
String expression,
Object[] paramMetadataArray);
I guess the hard part would be to parse the concatenated string from your lookup to get the two parameters expression and paramMeradataArray

expression and paramMeradataArray should be something like below:

"concat(X1,X2)", [S1, S2]
"substr(X1,X2,X3)", [S1, 1, 3]
Note that you must start the parameters with X and number them consecutively.

Update:

Assuming from lookup you are getting the port lkp='concat(S1,S2)~T1::substr(S3,1,3)~T2::substr(S3,3,1)~T3', you can try the following code in Java transformation. However, this code might not work for all functions. For example, passing decimal values will not work.

Import Packages:

import java.util.regex.Matcher;
import java.util.regex.Pattern;
On Input Row:

String[] arr1 = lkp.split("::");

for (int i = 0; i < arr1.length; i++) {
    String[] arr2 = arr1[i].split("~");
    Pattern pattern = Pattern.compile("\\((.*?)\\)");
    Matcher matcher = pattern.matcher(arr2[0]);
    String params = "";
    if (matcher.find()) {
        params = matcher.group(1);
    }
    String[] param = params.split(",");
    Object[] args1 = new Object[param.length];
    String params1 = "";
    for (int j = 0; j < param.length; j++) {
        int index = j + 1;
        Object arg = new Object();
        params1 = params1 + (!params1.equals("") ? "," : "") + "X"
                + index;
        if (param[j].equals("S1"))
            arg = S1;
        else if (param[j].equals("S2"))
            arg = S2;
        else if (param[j].equals("S3"))
            arg = S3;
        else if (param[j].equals("S4"))
            arg = S4;
        else if (!param[j].startsWith("\'"))
            arg = Integer.parseInt(param[j]);
        else
            arg = param[j];
        args1[j] = arg;
    }

    String exp = matcher.replaceAll("(" + params1 + ")");
    if (arr2[1].equals("T1"))
        T1 = (String) invokeJExpression(exp, args1);
    else if (arr2[1].equals("T2"))
        T2 = (String) invokeJExpression(exp, args1);
    else if (arr2[1].equals("T3"))
        T3 = (String) invokeJExpression(exp, args1);

}
answer Aug 7, 2014 by Shweta Singh
Similar Questions
+1 vote

I have a simple mapping that reads data from a SQL Server table with a column of type 'money'. The column is mapped to another table with the column of type decimal 19,4. (Informatica converts type money to type decimal 19,4.) When running the workflow, no rows are inserted into the target table, and no meaningful errors are produced. However the data is inserted correctly when the money column mapping is deleted.

Looking at the session log, it appears Informatica believes the rows were inserted correctly.

How can I correctly map the money column to a SQL server table.

+1 vote

I want to have compatible SQL for both Oracle database and Microsoft SQL server.

I want a compatible SQL expression that will return true for not null and not empty strings.

If I use:

column <> ''

it will work on Microsoft SQL server but not on Oracle database (as '' is null for Oracle)

If I use:

len(column) > 0

it will work on Microsoft SQL server but not on Oracle database (since it uses length() )

+2 votes

I am new to informatica. I am using INFORMATICA 9.1.0 and oracle 11g as source and target database. I tried to create one table in target database and tried to load data from source to target. Table is getting created in target database. and I created mapping and workflow which is valid and i start work flow but it gave me following error.

Message Code RR_4036

Message

Error connecting to database [ Arun ORA-00900: invalid SQL statement Database driver error... Function Name : executeDirect SQL Stmt : Arun Oracle Fatal Error Database driver error... Function Name : ExecuteDirect Oracle Fatal Error ].

please help me with good solutions.

Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...