Counting Rows using BPM in JDBC-File Scenario

[Former Member]

Hi Guys ,

I have a JDBC to File scenario wherein in I have to pick the data from two tables

1. Data Table with feilds SeqNumber ,GuiNumber , DocNumber.

2. Control Table with feilds Gui , TableName , Count , Status

one control record can have many data records.

My source xml is

<?xml version="1.0" encoding="utf-8"?>

<ns:inv_source_mt xmlns:ns="http://DBtoR3">

+<row>

++<SeqNumber>1</SeqNumber>

++<GUINumber>123</GUINumber>

++<DocNumber>ABCD</DocNumber>

++<Gui>123</Gui>

++<TableName>UIINV</TableName>

++<Count>2</Count>

++<Status>N</Status>

+</row>

+<row>

++<SeqNumber>2</SeqNumber>

++<GUINumber>123 </GUINumber>

++<DocNumber>EFGH </DocNumber>

++<Gui>123 </Gui>

++<TableName>UIINV </TableName>

++<Count>2</Count>

++<Status>N</Status>

+</row>

</ns:inv_source_mt>

Here the count feild of control record shows 2 and there are 2 Row records which is OK.

After fetching one control record and the corresponding data records

If the count feild of the control record doesnot match the number of data ( row) records then we need to update the status feild of the corresponding record with 'E'

elseif the count matches with the number of data records ( as in above xml) processing should continue and file should be generated

As i am new to BPM kindly help

[prabhu_s2]

Sunil

u mean in this kinda a scenario?

<?xml version="1.0" encoding="utf-8"?>

<ns:inv_source_mt xmlns:ns="http://DBtoR3">

+<row>

++<SeqNumber>1</SeqNumber>

++<GUINumber>123</GUINumber>

++<DocNumber>ABCD</DocNumber>

++<Gui>123</Gui>

++<TableName>UIINV</TableName>

++<Count>2</Count>

++<Status>N</Status>

+</row>

</ns:inv_source_mt>

where count (control record) is 2 and the number of data records (identified by the fields seqnumber , guinumber and doc number) is just one. If my understanding is right then map the ROW to COUNT node function and the result of COUNT node function to a UDF. Also the count source node to the udf which mean UDF will have two input one is the result of the COUNT std node function and another input is the source node count.

Inside the UDF check if the both the inputs are equal. If yes then return 'N' else return 'E'. Hope this helps

ROW----COUNT (STD node function)
             
              ----UDF---Target
             /
count(source)

Note: This can be done without a UDF and just by using EQUALS funtion with if..else in graphical mapping.

Message was edited by:

Prabhu S

[Former Member]

hi all ,

If the count of control record and number of data record doesnt match i need to update the status of control reocrd to 'E' in <b>the source database table</b> and not in the source xml

Regards,

[prabhu_s2]

Sunil

Recv the message inside a bpm. have the transformation step done. Use a switch node with condition set for Status = N. If the condition is true then use a send step in the true branch for sending the message to target. If condition is false have another mapping that caters the strucutre of JDBC recv and using a send step update the table using jdbc adapter.

[Former Member]

I suggest you to see the following link to learn more about mapping:

Mapping functionality in XI

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/9202d890-0201-0010-1588-adb5e89a...

SAP Exchange Infrastructure - Graphical_Mapping

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/6658bd90-0201-0010-fbb6-afe25fb3...

SAP Exchange Infrastructure - Graphical Mapping Exercise

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/fd5ebd90-0201-0010-d697-91374d5b...

SAP Exchange Infrastructure - Graphical Mapping - Advanced

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/be05e290-0201-0010-e997-b6e55f95...

SAP Exchange Infrastructure: Mapping Patterns - Understand Context Handling in Message Mapping - Webinar Powerpoint

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/f59730fa-0901-0010-df97-c12f071f...

SAP NetWeaver Exchange Infrastructure Mapping Troubleshooting - Webinar Powerpoint

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/e01e9400-9e81-2910-20a5-a862945a...

Mapping Lookups a RFC API

XI 3.0 New Mapping Features

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/8a57d190-0201-0010-9e87-d8f327e1...

[Former Member]

hi Sunil,

you can apply these condition in <b>Message Mapping</b> easily or you have some more requirment so define your <b>user define function</b>

hope this will help

regards

HONEY

[Former Member]

Do it with the Helo of Graphical Mapping Tool Inbuilt function

called <b>COUNT</b>

Just Map the row-->Count It will count the no. of row

then you have also the count field that has no. of entries..

So compare both in mapping and based on that map the required values.

So if Count = count(from paylod)..then map Count

Else Map Constant "E"

Thanks

Farooq.

*Rewards Points if you find it useful*