Retrieve data from DB and group records based on ID

, ,
1

Hi,

Could someone think of the logic that requires grouping of data based on ID. i have a scenerio that involves retrieving of data from DB, group it based on Group_ID and publish to a file share.

For example: If you look at the following table, I got to group data that has the same Group_ID (which is “1” in the following instance that has repeated thrice and so, have to group those three records into a single XML document)

Group_ID | Group_Name | Date | xmldata

1 | XYZ | 071905 | JHKJKJH

1 | XYZ | 071805 | KJHKJHH

2 | ABC | 010504 | KKJLHJLK

1 | XYZ | 070502 | KHKJHKJH

3 | MNU | 050502 | KJJHKHKJ

i was trying to implement it something like this:

*Get the data using a SP call
*Loop through the records
*???
*Append the records if they have same Group_ID (using AppendToDocList)
*Convert the document to a String
*Publish to a file share

but i got stuck in step 3 (tried solving it with Branch, Repeat etc., but didnt help…got confused on how to check if multiple records have the same ID).

Any thoughts will be greatly appreciated.

Thanks,

2

Why can’t you pull the data using groupby sql function and do the same logic above.

3

Hi Krishna,

Appreciate you response. I was unaware of the groupby SQL function in WM. could you be little detail on this.

Thanks,
John.

4

John,

Looking at the data sample you provided, it doesn’t seem like
you would be doing aggregation, i.e. sum(), avg(), etc.

In other words, if the target document needs to contain a separate element for each db record with the same group id, then a SQL ORDER BY would be more appropriate than a GROUP BY.

You would then loop over the result set, checking the group id to see if it has changed from the last saved value. You could do this test with a branch step doing an evaluate labels expression.

When the group id changes, you need to write the XML document out, and initialize a new document.

5

John,

Please do it as mark suggested,hope this is what you are looking for.

HTH,
RMG

6

Mark/RMG,

Thanks for the valuable suggestions. but i think groupby or orderby functions can be used for custom SQL services and since i’m calling a SP to retrieve the data, could you tell me if thats possible to include groupby or orderby functions in SP Adapter Service?

May be i’m missing something in here. Thanks for your time and support.

john.

7

I believe you have to include the orderby or groupby call in the SP(package/function)itself on the DB side.It is not possible mentioning in the SP adapterservice template.