Mind bender .. need help

tford

1) I have an "Existing External DB" BO called line_items which I can't change in any way.

2) Via a process, I've INSERTed multiple line_items IN an "AwareIM Automatic DB" BO called Order_Invoices. This piece works perfectly.

3) Need to calculate Order_Invoices.Z_1_base_price --> it's the sum line_items.unit_price*line.items.quantity of all the line items that were inserted in #2 above.

I can't use the aggregate SUM function because it would include math that can't be done in a sum (at least as far as I've found in testing).

Ideas?

BLOMASKY

why not use a stored procedure?

Bruce

hpl123

tford wrote
1) I have an "Existing External DB" BO called line_items which I can't change in any way.

2) Via a process, I've INSERTed multiple line_items IN an "AwareIM Automatic DB" BO called Order_Invoices. This piece works perfectly.

3) Need to calculate Order_Invoices.Z_1_base_price --> it's the sum line_items.unit_price*line.items.quantity of all the line items that were inserted in #2 above.

I can't use the aggregate SUM function because it would include math that can't be done in a sum (at least as far as I've found in testing).

Ideas?

Not sure I understand your use case fully but I can't see why a combination of SUM and COUNT with some simple arithmetic wouldn't work here. Can you share what you've tried and what doesn't work?
I mean SUM should work to SUM all LineItem.Unitprice in OrderInvoice and COUNT could count no of LineItem in OrderInvoice and then multiply these? or did I misunderstand what you are trying to do?