Home » SQL & PL/SQL » SQL & PL/SQL » Should a table have result of calculated fields?
Should a table have result of calculated fields? [message #280039] Mon, 12 November 2007 06:19 Go to next message
SilyGuy
Messages: 15
Registered: May 2005
Junior Member
Hi All,

I am desigining a table which will holds information of companies Balancesheet, IncomeStatements and Profit&Loss A/c. There will be certain fields which holds the simple or complex calculated results of several fields. My question is should I have a fields which will holds data of calculated fields or not? Those calculated fields can be used to calculate ratios. If I don't have those calculated fields in table then during ratio generation I will have to calculate those fields which may be complex.

Any help?

Thanks
Re: Should a table have result of calculated fields? [message #280042 is a reply to message #280039] Mon, 12 November 2007 06:23 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
What you could do is create views that have those calculations, and do your ratio calcs using the views
Re: Should a table have result of calculated fields? [message #280046 is a reply to message #280039] Mon, 12 November 2007 06:35 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you create a table which contains "calculated" columns, do you plan to maintain these values when other columns (which have participated in calculation) change?

For example:
SQL> create table test (a number, b number, calc number);

Table created.

SQL> insert into test (a, b) values (2, 3);

1 row created.

SQL> update test set calc = a * b;

1 row updated.

SQL> select * From test;

         A          B       CALC
---------- ---------- ----------
         2          3          6        --> 2 * 3 = 6

SQL> update test set a = 8;

1 row updated.

SQL> select * From test;

         A          B       CALC
---------- ---------- ----------
         8          3          6        --> 8 * 3 <> 6

How would you maintain 'calc = a * b'? Front end? Create a database trigger? Something different?

In my opinion, such a thing may be useful if you design some kind of a Data Warehouse. As it seems this is not a case here, I'd suggest not to store calculated values. A view, as Pablolee suggested, might do the job.

P.S. Pablolee, please note that I replied the correct message this time!
Re: Should a table have result of calculated fields? [message #280048 is a reply to message #280046] Mon, 12 November 2007 06:38 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
LOL, I should think so too Smile
P.S.
Just checking, but you got that I wasn't actually getting at you on the previous thread (sometimes when I try to make a joke it REALLY doesn't come across well)
Re: Should a table have result of calculated fields? [message #280052 is a reply to message #280046] Mon, 12 November 2007 06:48 Go to previous messageGo to next message
SilyGuy
Messages: 15
Registered: May 2005
Junior Member
Littlefoot wrote on Mon, 12 November 2007 07:35

If you create a table which contains "calculated" columns, do you plan to maintain these values when other columns (which have participated in calculation) change?

For example:
SQL> create table test (a number, b number, calc number);

Table created.

SQL> insert into test (a, b) values (2, 3);

1 row created.

SQL> update test set calc = a * b;

1 row updated.

SQL> select * From test;

         A          B       CALC
---------- ---------- ----------
         2          3          6        --> 2 * 3 = 6

SQL> update test set a = 8;

1 row updated.

SQL> select * From test;

         A          B       CALC
---------- ---------- ----------
         8          3          6        --> 8 * 3 <> 6

How would you maintain 'calc = a * b'? Front end? Create a database trigger? Something different?

In my opinion, such a thing may be useful if you design some kind of a Data Warehouse. As it seems this is not a case here, I'd suggest not to store calculated values. A view, as Pablolee suggested, might do the job.

P.S. Pablolee, please note that I replied the correct message this time!


There will be a front-end application in VB which will be used for any Addition and Modification of the data. If any field is modified the changes will be reflected automatically in the calculated field. So that's not a problem. In 99% cases once the data is entered it will remain static, there will be no frequent changes. It will work as dataware house. My concern is that am I creating some extra columns?

Any suggestion?

Thanks

[Updated on: Mon, 12 November 2007 06:49]

Report message to a moderator

Re: Should a table have result of calculated fields? [message #280054 is a reply to message #280052] Mon, 12 November 2007 06:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
SilyGuy wrote on Mon, 12 November 2007 13:48

If any field is modified the changes will be reflected automatically in the calculated field. So that's not a problem.


Dare to bet on that?
Re: Should a table have result of calculated fields? [message #280056 is a reply to message #280039] Mon, 12 November 2007 06:57 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
From what you've shared, I would not create an extra column for this.

I would do 1 of the 2 things already suggested here.

Either
Create a view which will include the calculated value (This guaruntees integerity

or
Do the calculations at display time.


Re: Should a table have result of calculated fields? [message #280057 is a reply to message #280054] Mon, 12 November 2007 07:00 Go to previous messageGo to next message
SilyGuy
Messages: 15
Registered: May 2005
Junior Member
Frank wrote on Mon, 12 November 2007 07:55

SilyGuy wrote on Mon, 12 November 2007 13:48

If any field is modified the changes will be reflected automatically in the calculated field. So that's not a problem.


Dare to bet on that?


Yes because changes will be made thru a VB form and the application takes care of the changes made and it is reflected in the calculated columns.

Re: Should a table have result of calculated fields? [message #280059 is a reply to message #280052] Mon, 12 November 2007 07:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

My concern is that am I creating some extra columns?

The different options will be given: either create a trigger to fill the column or create a view.
In 11g, you can create a virtual column:
SQL>  create table t (a integer, b integer, c as (a+b) virtual);

Table created.

SQL> insert into t (a,b) values (2, 3);

1 row created.

SQL> select * from t;
         A          B          C
---------- ---------- ----------
         2          3          5

1 row selected.

SQL> update t set a = 8;

1 row updated.

SQL> select * from t;
         A          B          C
---------- ---------- ----------
         8          3         11

1 row selected.

Regards
Michel
Re: Should a table have result of calculated fields? [message #280061 is a reply to message #280057] Mon, 12 November 2007 07:03 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
SilyGuy wrote on Mon, 12 November 2007 14:00

Yes because changes will be made thru a VB form...
Well, there's your guarantee then Very Happy. I don't trust that, but it's your party.

@Michel: 11g seems more and more interesting.

MHE
Re: Should a table have result of calculated fields? [message #280070 is a reply to message #280061] Mon, 12 November 2007 07:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maarten,
Yes I think so.
Virtual columns are great.
Results can be stored or generated on the fly.
These columns can be indexed.
You can partition on these columns.

Many things can be done easier with that.

Regards
Michel
Re: Should a table have result of calculated fields? [message #280078 is a reply to message #280059] Mon, 12 November 2007 07:27 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Virtual Columns? Very, very cool. I might need to find myself some time to have a look at 11 sooner rather than later
Re: Should a table have result of calculated fields? [message #280079 is a reply to message #280039] Mon, 12 November 2007 07:31 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Kind of funny actually, because RDB (Yes I go back that far) had virtual columns from the beginning. Once they were bought out by Oracle they went away.

Glad to see them back.

Re: Should a table have result of calculated fields? [message #280090 is a reply to message #280079] Mon, 12 November 2007 07:57 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Hmm, I was going to mention virtual columns too. I used to user R:Base back in the early 90's and they had what was called a "calculated column." Did R:Base evolve into RDB?


And back to the original poster, what happens when someone updates the data through SQL*Plus, or when one of the columns get updated but the VB program fails for one reason or another? Bad design.
Re: Should a table have result of calculated fields? [message #280112 is a reply to message #280070] Mon, 12 November 2007 09:03 Go to previous message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
2 corrections on what I said:
- in the current version, virtual columns are always generated at each access and never stored, so take care of expression complexity
- there are restrictions on virtual columns; from doc:
Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables.

Regards
Michel
Previous Topic: doubt regarding exception handling
Next Topic: delete hierarchical data
Goto Forum:
  


Current Time: Wed Dec 07 02:56:05 CST 2016

Total time taken to generate the page: 0.14830 seconds