OraFAQ Forum: Forms » calculating totals conditionally

Home » Developer & Programmer » Forms » calculating totals conditionally

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

calculating totals conditionally [message #175828]

Mon, 05 June 2006 11:01

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

I am using calculation mode to keep a running total of a column in a form, but what I need is to have this running total only for rows that have a specific value for one of the other columns.

How would I implement this now? Calculation mode doesn't seem to meet my needs here.

Report message to a moderator

Re: calculating totals conditionally [message #175911 is a reply to message #175828]

Tue, 06 June 2006 01:20

emadbsb
Messages: 334
Registered: May 2005
Location: egypt

Senior Member

get your sum using a query and that query must have the condition that you want

e.g.

Quote:

select sum(value) from 'table_name'
where value <> 1;

here we get the sum of the column value that didnot include rows containing '1'

[Updated on: Tue, 06 June 2006 01:21]

Report message to a moderator

Re: calculating totals conditionally [message #176025 is a reply to message #175911]

Tue, 06 June 2006 07:40

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

But they are not yet saved to the table, therefore a query like that returns no data.

This is a multi record block and multiple rows are entered before a commit is issued.

Report message to a moderator

Re: calculating totals conditionally [message #176047 is a reply to message #176025]

Tue, 06 June 2006 10:22

sciolist
Messages: 4
Registered: June 2006
Location: Cambridge, UK

Junior Member

How many records are you needing to sum? Is a first_record/next_record loop with a sum variable too ugly and slow?

Report message to a moderator

Re: calculating totals conditionally [message #176056 is a reply to message #176047]

Tue, 06 June 2006 12:15

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Anywhere from 1 to 10 or so. What do you mean the first record/last record loop?

Report message to a moderator

Re: calculating totals conditionally [message #176183 is a reply to message #176056]

Wed, 07 June 2006 04:51

sciolist
Messages: 4
Registered: June 2006
Location: Cambridge, UK

Junior Member

Something like this:

BEGIN
v_sum := 0;
v_currec := :SYSTEM.CURSOR_RECORD;
v_curfld := :SYSTEM.CURSOR_FIELD;
go_block('my_block');
FIRST_RECORD;
LOOP
IF :item1 = v_OKtoSum then
v_sum := v_sum + :item2;
:run_total := v_sum;
END IF;
IF :SYSTEM.LAST_RECORD = 'TRUE' THEN
EXIT;
ELSE
NEXT_RECORD;
END IF;
END LOOP;
GO_RECORD(v_currec);
GO_FIELD(v_curfld);
END;

The currec and curfield take focus back to where itt was before you analysed the block.

Report message to a moderator

Re: calculating totals conditionally [message #176247 is a reply to message #176183]

Wed, 07 June 2006 08:58

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Thanks. I wasn't quite sure what you were referring to with first_record/next_record loop but then came up with a similar piece of code.

Report message to a moderator

Re: calculating totals conditionally [message #176261 is a reply to message #176247]

Wed, 07 June 2006 09:25

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Just an FYI, I didn't like the looping method, so I created a non-database TEXT-ITEM in the same block. I used a POST-CHANGE trigger on the database column to load the data into the non-database TEXT-ITEM if the other column was a certain value. Then I made another TEXT-ITEM in calculation mode to sum that new non-database TEXT_ITEM.

Report message to a moderator

Re: calculating totals conditionally [message #176597 is a reply to message #176261]

Thu, 08 June 2006 19:51

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Don't use Post-Change.

David

Report message to a moderator

Previous Topic:	Creating hint/prompt text interactively
Next Topic:	Not fetching details for a master block

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 08:27:48 CDT 2024