UDT Insert and Update

[Former Member]

I have created a UDT and related SQL Queries. I have individual queries that perform the following functions.

1) Insert

2) Update

3) Read

4) Delete

When I run each query individually in succession I get the desired results, but when I try to combine the queries in one statement I get a "duplicate pk error".

My questions are as follows:

Can I combine these types of queries in one statement within B1?

Can I use the 'SBO_SP_TransactionNotification' to combine these functions?

Can I create my own SP to modify a UDT?

Much thanks,

Rob

[Former Member]

Rob,

Some of SQL commands can not be coexisted in one statement. You have to separate them for avoiding the error you got. I would wonder in what logic you can give to allow them in one statement. Please give an example

So the answers are:

Can I combine these types of queries in one statement within B1? Mostly No depends on your logic

Can I use the 'SBO_SP_TransactionNotification' to combine these functions? Yes/No depends on your logic

Can I create my own SP to modify a UDT? Yes

Thanks,

Gordon

[Former Member]

Thanks Gordon. My logic is as follows:

I have written a report that shows the sum of $ changes to a Sales Order over a period of time.

1) Insert the most recent version of the SO in a UDT, mark as "C"

2) Update 1 above to include $

3) Insert the latest version of the SO prior to the period in question, mark as "P"

4) Update 3 above to include negative $

5) Insert sum of records in UDT group on SO number, mark as "R"

6) Delete all "C" and "P" records.

I then run a report based on the UDT to find the value of changed orders. We do this because we are a retail store with a 4 week lead time. During this period customers often change their mind and we like to capture this info.

Thanks for your help.

Rob

[Former Member]

According to your logic, separated statement is a must. I think you could simplify it by combine 1&2 to one insert. Same as 3&4.

Not sure what you mean by 5. Number 6 should be a single statement always.