Problem:
Overview:
The PowerCenter Client uses the Java Development Kit (JDK) to compile the Java code and generate byte code for the transformation. When you run a session with a Java transformation, the Integration Service uses the Java Runtime Environment (JRE) to execute the byte code and process input rows and generate output rows. A Java Transformation can be active or passive. The type is set when a transformation is created and it cannot be changed later.
A Java Transformation always has one input group and one output group:
A Java Transformation can have input ports, output ports and input/output ports (treated as pass-through ports):
The ports can also be added to a Java transformation by dragging and dropping ports from another transformation (they are created as input/output ports):
The default code window will look like this:
Here are available methods desctibed:
| Method | Description |
| commit | generates a transaction |
| failSession | throws an exception with an error message and fails the session |
| generateRow | generates an output row for active Java transformations |
| getInRowType | returns the input type of the current row in the transformation |
| incrementErrorCount | increases the error count for the session |
| isNull | checks the value of an input column for NULL |
| logError | writes an error message to the session log |
| logInfo | writes an informational message to the session log |
| rollback | generates a rollback transaction |
| setNull | sets the value of an output column to NULL |
| setOutRowType | sets the update strategy for output rows |
All you need to add is basically like this:
for (int i = 1; i <= in_COUNT; i++) {
generateRow();
}
Of course in_COUNT should contain the pre-calculated number of output ports needed.
Solution:
So, how can we achieve the list of RequestIds as stated in the beginning? Here's the overview of the key part of the mapping:
First, let's prepare some data for the transformation. In this example I've used a dummy variable port as the data source - the incoming data will be clearly visible:
- RequestIdList (123,456,789,)
- RequestCount (3)
Note the extra comma in the end. It will come in handy later. Than, there's the Java transformation with the following ports:
The RequestIdList and the sequence id will be needed downstream to extract appropriate substring. Here's the code:
RequestSeq values are created here. It is used in the last expression:
RequestId port will have the appropriate id extracted from the list. Here's the ugly code for that:
IIF(RequestSeq=1,
SUBSTR(RequestIdList,
RequestSeq,
INSTR(RequestIdList,',',RequestSeq-1,RequestSeq)
-1),
SUBSTR(RequestIdList,
INSTR(RequestIdList,',',RequestSeq,RequestSeq-1)+1,
INSTR(RequestIdList,',',RequestSeq,RequestSeq)-INSTR(RequestIdList,',',RequestSeq,RequestSeq-1)-1
)
)
Each time a new sequence is started, substring from first character to the first occurence of separator is taken. Later on tere is a calculation to determine the index of separator and the length the next separator. This is where adding the comma (which in this case is the separator) is handy - for the last substring the formula doesn't change.
That's it! But... there is a nicer way to achieve it too.
Alternate solution:
As splitting the list is not convenient, we can use the already created Java transformation to handle this part as well. All we need in this case is the input string and the number of Ids. Here's the modified transformation:
String[] temp;
String delimiter = ",";
temp = RequestIdList.split(delimiter);
for (int i = 0; i < RequestCount; i++) {
RequestSeq=i;
Request= temp[i];
generateRow();
}
Or, even simplier (without any need for pre-calculating the count):
String[] temp;
String delimiter = ",";
temp = RequestIdList.split(delimiter);
for (int i = 0; i < temp.lenght; i++) {
RequestSeq=i;
Request= temp[i];
generateRow();
}
This solution is by far simpllier to implement and maintain.
