Home » SQL & PL/SQL » SQL & PL/SQL » Adding Column Values (Oracle / Apex)
Adding Column Values [message #351941] Fri, 03 October 2008 15:40 Go to next message
greendba
Messages: 22
Registered: September 2008
Location: Toronto
Junior Member
Newbie question here.

Lets say I have have a table that tracks item orders.
Simply put...

col1
col2
col3
colTotalof123

How can I calculate the sum value of col1, col2 and col3 and insert that value into colTotalof123 and of course save the record.

I'm using APEX and trying to get this into computation... but I'm wondering if it is at all possible...

Thanks!
Re: Adding Column Values [message #351944 is a reply to message #351941] Fri, 03 October 2008 15:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
update table set colTotalof123=col1+col2+col3

Regards
Michel
Re: Adding Column Values [message #351946 is a reply to message #351944] Fri, 03 October 2008 15:46 Go to previous messageGo to next message
greendba
Messages: 22
Registered: September 2008
Location: Toronto
Junior Member
I don't want to update the table later, I want it to happen on submission... Is this possible?
Re: Adding Column Values [message #351948 is a reply to message #351946] Fri, 03 October 2008 15:52 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, but - in my opinion - it is probably useless.

Why would you want to store sum of these columns in the same table? There's no use to do that, as every time you update a column, you'd have to maintain the summary column as well. Besides, any time you need to know the result of "col1 + col2 + col3", do it on-the-fly. Unless you are talking about some kind of a data warehouse (but your question doesn't sound like that).

Finally, a possible way to do that is by use of the database trigger.
Re: Adding Column Values [message #351950 is a reply to message #351948] Fri, 03 October 2008 15:57 Go to previous messageGo to next message
greendba
Messages: 22
Registered: September 2008
Location: Toronto
Junior Member
It may be useless to you, but that's what required of me.

This is not an uncommon feature... if you look at any online ordering system, if you select 1,2 or 3 of an item, the grand total is updated as select each additional item. This no different... ?

Re: Adding Column Values [message #351964 is a reply to message #351950] Sat, 04 October 2008 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Rename the table, create a view upon the table with previous table name and use the view instead of the table.

Regards
Michel
Re: Adding Column Values [message #351968 is a reply to message #351950] Sat, 04 October 2008 01:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
greendba wrote on Fri, 03 October 2008 22:57
It may be useless to you, but that's what required of me.

This is not an uncommon feature... if you look at any online ordering system, if you select 1,2 or 3 of an item, the grand total is updated as select each additional item. This no different... ?



The DISPLAYED grand total is updated. This does not mean that it is stored in the database!
Use calculations at retrieval-time. Believe us, it will save you tons of headaches.
Re: Adding Column Values [message #351973 is a reply to message #351968] Sat, 04 October 2008 01:58 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
since greendba is using apex for compuation on submission,
he requires the way to adopt computation firing logic on submission button. if his tries on this aspect, i like to know what sort of problem he is facing. if greendba is on different footing, ignore this message
yours
dr.s.raghunathan
Re: Adding Column Values [message #352183 is a reply to message #351941] Mon, 06 October 2008 09:16 Go to previous messageGo to next message
greendba
Messages: 22
Registered: September 2008
Location: Toronto
Junior Member
OK, thanks for all your input so far.

Why couldn't I create an BEFORE/AFTER trigger containing the following code

update :new.col123total=:col1+:col2+col3

If this values are already stored in the session why can't I pull them, add them together and update a single column. We all do similar things with record timestamp, or user tracking triggers... example below?

select CURRENT_TIMESTAMP into :new.recordtimestamp from dual;

This must be possible, I just don't know the syntax.

You continued help is appreciated.

Thanks!
Re: Adding Column Values [message #352197 is a reply to message #352183] Mon, 06 October 2008 10:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Application Developer's Guide - Fundamentals
Chapter 9 Coding Triggers

Of course you don't care about the warnings we posted you and the fact that you are out of relational model.

Regards
Michel
Re: Adding Column Values [message #352203 is a reply to message #352197] Mon, 06 October 2008 10:39 Go to previous messageGo to next message
greendba
Messages: 22
Registered: September 2008
Location: Toronto
Junior Member
I appreciate the link to info... Michel

However, please check your sarcasm at the login... I do care and have taken into account all comments posted so far... And yours in particular have been helpful but it is my own ignorance that is hindering a complete resolution.

The communal efforts here do no go unappreciated by me...

Cheers.

Eric
Re: Adding Column Values [message #352205 is a reply to message #352203] Mon, 06 October 2008 10:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
My apologies for the "of course" but I really insist that you must not include such a column... but maybe it is just an exercise.

Regards
Michel
Re: Adding Column Values [message #352428 is a reply to message #351941] Tue, 07 October 2008 10:12 Go to previous messageGo to next message
greendba
Messages: 22
Registered: September 2008
Location: Toronto
Junior Member
Problem solved... It's so much simpler than the road I was going down.

An update/insert trigger on the underlying table with the following PL/SQL code works just fine...

:new.total := :new.col1 + :new.col2 + :new.col3;

The only problem is that if any of the columns are null then it add to NULL...
My initial thought is to create a case statement changing the values to 0 if they are null...
I tried setting the default value to 0 but that doesn't kick in until after the trigger so that's no good.

Any suggestions for solving the NULL value issue?

Thanks,
Eric

[Updated on: Tue, 07 October 2008 10:58]

Report message to a moderator

Re: Adding Column Values [message #352589 is a reply to message #352428] Wed, 08 October 2008 08:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
:new.total := nvl(:new.col_1,0) + ....


Re: Adding Column Values [message #352645 is a reply to message #352589] Wed, 08 October 2008 14:57 Go to previous message
greendba
Messages: 22
Registered: September 2008
Location: Toronto
Junior Member
Thanks JRowbottom

That is a lot simpler than if or while etc...

Previous Topic: multiple rows in one column
Next Topic: Where Like or In
Goto Forum:
  


Current Time: Sun Dec 11 04:18:11 CST 2016

Total time taken to generate the page: 0.11610 seconds