BODS: How to load data row by row in sequence with Case Transform?

[former_member593418]

Assume that Source_Table and Target_Table contains row as below.

Requirement:

1) If TRANSACTION_TYPE=ORDER_CHANGE, then update column SHIP_TO_CITY, and LAST_TRANSACTION_DATE in Target_Table.

2) If TRANSACTION_TYPE=ORDER_STATUS, then, update column STATUS, and LAST_TRANSACTION_DATE in Target_Table.

3) Row in Source_Table must be sorted/ordered by ROW_ID in ascending, before loading into Target_Table.

Problem:

Below is my data_flow design, but it cannot meet requirement #3, as row in Source_Table is NOT loaded into Target_Table by ROW_ID sequence, even it has been ordered by ROW_ID in Query_Transform "QUERY_ORDER".

From my observation (via Debugging), BODS may load rows with ROW_ID = 1, 4, which has TRANSACTION_CODE=ORDER_CHANGE into Target_Table.

Then loads row with ROW_ID 2, 3, 5, 6, which has TRANSACTION_CODE=ORDER_STATUS into Target_Table.

It should load row with ROW_ID 1,2,3,4,5,6 in sequence into Target_Table.

Does this issue is due to Case_Transform "CASE", which placed after Query_Transform "QUERY_ORDER"?

Can u advise?

[former_member187605]

Yes, I can, but again, it makes no sense.

You can Merge the two branches from the Case and then sort (in a Query transform) before going into the (single) Table_Comparison.

[former_member593418]

Hi! Dirk...Thanks for your advice.

Can u advise any possible technique that can be used to resolve requirement (to preserve the order of the original set of operation) as mentioned above?

BTW, found that there is Map_CDC_Operation transform that normally used together with Mainframe CDC or Relational CDC.

But, I not used Mainframe CDC or Relational CDC.

Reference: https://help.sap.com/doc/55f768a2553b4cb9b89ee49ddf1b7595/4.2.10/en-US/ds_42_reference_en.pdf

Screenshot:

Thanks.

[former_member187605]

This is standard behaviour. You can never control the order in which data is stored in a relational table. As there is no "order by"-clause in an SQL INSERT statement.

[former_member593418]

Hi! Dirk...Thanks for your advice.

Can u advise any possible method to resolve requirement (to preserve the order of the original set of operation) as mentioned above?

BTW, found that there is Map_CDC_Operation transform that normally used together with Mainframe CDC or Relational CDC.

But, I not used Mainframe CDC or Relational CDC.