Re: Help in solving queries.......

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Steve Kass (skass_at_drew.edu)
Date: 01/27/05 

Date: Wed, 26 Jan 2005 21:11:30 -0500

John,

  To give Patrick the benefit of the doubt, note that he
mentioned needing the quotient "for historical purpose .. later."
It might be completely reasonable to update (i.e., set) the quotient
column A in an insert trigger, so that column A stores the original
ratio of B to C.

  From what Patrick has said, I'd assume the value of column A
represents "initial B/C ratio," not "current B/C ratio." The latter,
which he may or may not need, can be calculated on the fly as
you note.

  Another reason to store a computed value is when data is
archived, if there is an advantage to indexing the computed
column. Indexed views meet some of these kinds of
needs, but they are more restrictive and less simple. And
the data integrity an indexed view provides can be insured
with a table constraint of CHECK (A = 1.00*B/C), though
I'd only recommend this if the quotient were stored as a
decimal type, not a float.

Steve Kass
Drew University

John Vinson wrote:

>On Wed, 26 Jan 2005 16:27:10 -0600, "Patrick" <kingofusa@juno.com>
>wrote:
>
>
>
>>Hi Group
>>
>>I want to divide one column by another column and stored that value into
>>third column.
>>
>>
>
>Storing derived data such as this in your table accomplishes
>three things: it wastes disk space; it wastes time (almost
>any calculation will be MUCH faster than a disk fetch); and
>most importantly, it risks data corruption. If one of the
>underlying fields is subsequently edited, you will have data
>in your table WHICH IS WRONG, and no automatic way to detect
>that fact.
>
>Just redo the calculation whenever you need it, either as a
>calculated field in a Query or just as you're now doing it -
>in the control source of a Form or a Report textbox.
>
>
>
>>Example :
>>Column A, Column B, Column C
>>
>>I want to divide Column B by Column C and stored that value within Column A
>>
>>Basically Column A is %
>>Column B < Column C ( always )
>>
>>When I use division "/" operator it returns 0 into the Column A
>>
>>Update Table Set Column A = Column b/Columb C
>>it gives 0 (zero) in column A
>>Let me know what I am doing wrong
>>
>>Column A : float
>>column b and column c are int
>>
>>
>
>That's the problem. An int divided by an int gives you an int.
>
>Try using a Query to dynamically calculate A:
>
>A: Csng([b]) / Csng([c])
>
>If you insist you can use the same expression in your update query,
>but don't complain if you end up with INVALID DATA in your database!
>
> John W. Vinson[MVP]
>
>

Relevant Pages

  • Re: Help in solving queries.......
    ... It might be completely reasonable to update the quotient ... Another reason to store a computed value is when data is ... >any calculation will be MUCH faster than a disk fetch); ... An int divided by an int gives you an int. ...
    (microsoft.public.sqlserver.dts)
  • Re: Help in solving queries.......
    ... It might be completely reasonable to update the quotient ... Another reason to store a computed value is when data is ... >any calculation will be MUCH faster than a disk fetch); ... An int divided by an int gives you an int. ...
    (microsoft.public.sqlserver.tools)
  • Re: Help in solving queries.......
    ... It might be completely reasonable to update the quotient ... Another reason to store a computed value is when data is ... >any calculation will be MUCH faster than a disk fetch); ... An int divided by an int gives you an int. ...
    (microsoft.public.sqlserver.programming)
  • Re: Help in solving queries.......
    ... It might be completely reasonable to update the quotient ... Another reason to store a computed value is when data is ... >any calculation will be MUCH faster than a disk fetch); ... An int divided by an int gives you an int. ...
    (microsoft.public.dotnet.faqs)