IDoc to JDBC scenario - UDF

[Former Member]

Hi,

Hope you all are doing well. I have an IDoc to JDBC scenario. The IDoc structure is as follows:

<IDOC BEGIN="1">

<Structure 1>

<DEFINITION>xxxx</DEFINITION>

<CREATE_DATE>20070706</CREATE_DATE>

</Structure 1 SEGMENT="1">

<Structure 2>

<ELEMENT>xxx1</ELEMENT>

<CREATE_DATE>20070719</CREATE_DATE>

</Structure 2>

<Structure 2>

<ELEMENT>xxx2</ELEMENT>

<CREATE_DATE>20070719</CREATE_DATE>

</Structure 2>

<Structure 3>

<ELEMENT>xxx1</ELEMENT>

<PROJECT>yyyy1</DEFINITION>

</Structure 3>

<Structure 3>

<ELEMENT>xxx1</ELEMENT>

<PROJECT>yyyy2</PROJECT>

</Structure 3>

<Structure 3>

<ELEMENT>xxx2</ELEMENT>

<PROJECT>yyyy3</DEFINITION>

</Structure 3>

<Structure 3>

<ELEMENT>xxx2</ELEMENT>

<PROJECT>yyyy4</PROJECT>

</Structure 3>

</IDOC>

Structures 1, 2 and 3 are at the same level/hierarchy in the IDoc. Structure 3 contains the project name of records in structure 2. The requirement is that if the records in structure 2 have the create date as the current date then the corresponding project in structure 3 has to be passed on to the database by creating a statement. To do this, we need to check the date of the record in structure 2 and if the date is equal to the current date then we have to use the element in structure 2 to select the corresponding rows in structure 3 to be passed on to the database. The INSERT statements have to be created for each of the rows of Structure 3 to be passed on to the database.

How do I achieve this?

I tried using node and text standard functions functions but was unable to achive the objective. Can I use UDF to read the IDoc structures and determine the statements?

regards

Debansu

[markangelo_dihiansan]

Hello,

You can try the logic below:

Structure2/CREATE_DATE -> removeContext -> CompareDates(yyyyMMdd) -> equalsA -> ifWithoutElse -> yourUDFLogicHere
                  CurrentDate(yyyyMMdd) -> /          Constant: 0 -> /                /
                                     PROJECT -> removeContext -> formatByExampple -> /
Structure3/ELEMENT -> removeContext -> SplitByValue:ValueChanged -> /

Hope this helps,

Mark

[Former Member]

Mark,

Can you please elaborate on the logic that you have provided? Can you please provide the logic for UDF if it is required for achieving the objective?

regards

Debansu

[markangelo_dihiansan]

Hello,

Just before the UDF Logic, the message mapping does what is your requirement which is to pass only the records of structure 3 if the date in structure 2 matches the current date. Now with regards to the yourUDFlogicHere, I am unsure what to put since you have not provided your XML SQL structure. If you can at least provide the structure, it would help us in finalizing the mapping.

Hope this helps,

Mark

[Former Member]

Hi Debansu,

Use this Queue type UDF

public void UDF(String[] curDate, String[] Element_Str2, String[] date, String[] Element_Str3, String[] project, ResultList result, Container container) throws StreamTransformationException{

for(int i=0; i<date.length; i++)

{

if (curDate[0].equals(date<i>))

{

for(int j=0; j<project.length; j++)

{

if (Element_Str2<i>.equals(Element_Str2[j]))

result.addValue(project[j]);

}

}

}

}

Here first parameter is current date function, second parameter is 2nd structure element field, third parameter is date field from 2nd structure, fourth parameter is 3rd structure element field and fifth parameter is project field from 3rd structure.

Regards,

VR