Computed Column Confusion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Computed Column Confusion

Author

Topic

earwicker
Starting Member

3 Posts

Posted - 2003-05-20 : 17:26:57

Hello All---

I'm creating a small database that tracks a cyclist's daily rides. I want to use two computed columns to get the rolling MPH (MPH for time actually spent on the bike) and the elapsed MPH (MPH after factoring in time off of the bicycle). I enter the elapsed time in seconds, the rolling time in seconds, and the mileage. The computation is then simple enough:

rollSpeed = mileage / (rollTime / 3600)
elapsedSpeed = mileage / (elapsedTime / 3600)

I include these equations in my table creation code like this:


    --calculated cols
    rollSpeed    AS mileage /  (rollTime  / 3600),
    elapsedSpeed AS mileage /  (elapsedTime / 3600),
    ...

But it doesn't work. It just assigns the value of 'mileage' to rollSpeed and elapsedSpeed.
In fact, it doesn't make any difference whatsoever what value I place inside the
parentheses: 3600, 3200, 50000 . . .
I always get the mileage field as my speed value!.
Adding the parentheses seems to kill it.

If I remove the parentheses, the calculations are not what I want, but they do WORK. I'm mystified (not the first time, not the last, I'm sure).

  
    --calculated cols
    rollSpeed    AS mileage /  rollTime  / 3600,
    elapsedSpeed AS mileage /  elapsedTime / 3600,
    ...

This gives me real, calculated values back! What's going on?!?!?!?

Here is the complete table creation code (with the parentheses)--followed by the INSERT statement I'm using to test it:


CREATE TABLE Rides
(
    --cols
    rideID       int  IDENTITY NOT NULL,
    event        int           NOT NULL DEFAULT 9,
    bikeUsed     int           NOT NULL DEFAULT 1,
    rideDate     datetime      NOT NULL,
    rollTime     int           NOT NULL,    -- in seconds
    elapsedTime  int           NOT NULL,    -- in seconds
    odometer     float(32)         NULL,
    mileage      float(32)     NOT NULL,
    location     varchar(  50) NOT NULL DEFAULT 'Hometown, USA',
    notes        varchar(1000)     NULL,

    --calculated cols
    rollSpeed    AS mileage /  (rollTime  / 3600),
    elapsedSpeed AS mileage /  (elapsedTime / 3600),
    offBikeTime  AS elapsedTime - rollTime,

    --constraints
    CONSTRAINT PK_rideID
        PRIMARY KEY( rideID ),
    CONSTRAINT FK_rideEvent
        FOREIGN KEY( event ) REFERENCES Events( eventID ),
    CONSTRAINT FK_bikeUsed
        FOREIGN KEY( bikeUsed ) REFERENCES Bicycles( bikeID )
)

--INSERT STATEMENT
INSERT INTO Rides( rideDate, rollTime, elapsedTime, mileage) 
	VALUES( GetDate(), 3600, 3700, 20.4 )

TIA,

---earwicker[ch]

Edited by - earwicker on 05/20/2003 17:28:40

chadmat
The Chadinator

1974 Posts

Posted - 2003-05-20 : 17:37:45

Why do you want to store those values? Why not create a view that makes the calculation for you?

I haven't tried this, but you might try putting [] around the entire calculation.

Like this:

--calculated cols
rollSpeed AS [mileage / (rollTime / 3600)],
elapsedSpeed AS [mileage / (elapsedTime / 3600)],

Or maybe parenthases. Or see below

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Edited by - chadmat on 05/20/2003 17:43:52

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-20 : 17:39:07

When you say (rollTime / 3600) you're dividing two integer values, which yields an integer.
Try this:


    rollSpeed    AS mileage /  (rollTime  / 3600.0),
    elapsedSpeed AS mileage /  (elapsedTime / 3600.0),

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-20 : 17:39:59

rollTime / 3600 and elapsedTime / 3600 will be truncated to integers before the divide which is probably why you are just seeing the mileage.

Try mileage * 3600 / rollTime

or see the above :).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 05/20/2003 17:40:59

earwicker
Starting Member

3 Posts

Posted - 2003-05-20 : 17:44:15

thanks guys . . . it was the integer / integer problem that was the culprit.

I changed it to 3600.0 (in both cases) and it works just perfectly.

Much obliged,

---earwicker[ch]

earwicker
Starting Member

3 Posts

Posted - 2003-05-20 : 18:15:49

quote:

Why do you want to store those values? Why not create a view that makes the calculation for you?

chad---

I'm under the impression that SQLServer does NOT store these values (unless I decide to index the computed column). Is my information wrong?

---earwicker[ch]

chadmat
The Chadinator

1974 Posts

Posted - 2003-05-21 : 12:54:01

Once again, I am wrong. (Twice in the same day

)

It isn't stored. My Bad.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-21 : 13:12:03

I think we had a thread some time back about there being differences in execution plans produced for the same query using calculated columns and views. I can't remember the details.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-05-22 : 16:37:53

Arnold = 'can't remember '

returns

Data does not compute

Jim = 'can't remember '

Returns

Standard operating procidure

LOL

Jim
Users <> Logic

Edited by - jiml on 05/22/2003 16:41:33

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources