Mind bender .. need help

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Mind bender .. need help

Post by 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?
Tom - V8.8 build 3137 - MySql / PostGres
BLOMASKY
Posts: 1470
Joined: Wed Sep 30, 2015 10:08 pm
Location: Ocala FL

Re: Mind bender .. need help

Post by BLOMASKY »

why not use a stored procedure?

Bruce
hpl123
Posts: 2579
Joined: Fri Feb 01, 2013 1:13 pm
Location: Scandinavia

Re: Mind bender .. need help

Post by 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?
Henrik (V8 Developer Ed. - Windows)
Post Reply