Integer In, Decimal Out - 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

Transact-SQL (2000)

Integer In, Decimal Out

Author

Topic

AndyC
Yak Posting Veteran

53 Posts

Posted - 2005-02-12 : 16:48:40

I wish to do a mathematical expression on a bunch of variables of data type integer but get a result to one decimal place
The expression is


POWER(pts + reb + asts + stl + blk + fgm - fga + ftm - fta - turnover, 0.75) / 21

I have tried


cast(POWER(pts + reb + asts + stl + blk + fgm - fga + ftm - fta - turnover, 0.75) / 21 as numeric)

and the convert equivalent but just end up with integer result
Also I changed one of the variables data-types to numeric but
this resulted in an error when running the query

Andrew Clark
www.majorleaguecharts.com

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-12 : 17:00:18

convert(decimal(18,1),(POWER(0.0 + pts + reb + asts + stl + blk + fgm - fga + ftm - fta - turnover, 0.75) / 21)

==========================================
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.

AndyC
Yak Posting Veteran

53 Posts

Posted - 2005-02-13 : 11:40:29

Cheers - worked a treat

Andrew Clark
www.majorleaguecharts.com

AndyC
Yak Posting Veteran

53 Posts

Posted - 2005-02-14 : 14:10:06

Sorry one more point
I thought you could ORDER BY an expression in the select list

However I get zero returns with


SELECT     TOP 10 ilkid, reb,pts, firstname + N' ' + lastname AS name,
		CONVERT(decimal(18, 1), 
                      POWER(0.0 + pts + reb + asts + stl
                       + blk + fgm - fga + ftm
                       - fta - turnover, 0.75) / 21) AS AV, 
                      ROUND(POWER(((pts / CAST(gp AS numeric)) 
                      * (reb / CAST(gp AS numeric))) 
                      * (asts / CAST(gp AS numeric)), 0.33),1) AS VI
FROM         basketball.Player_regular_season 
WHERE     ([year] = 2003)
ORDER BY  5  DESC

If alternatively I try ORDER BY 2, the data is returned correctly by reb descending

Andrew Clark
www.majorleaguecharts.com

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-14 : 14:24:08

When you say you get 0 returns do you mean no data at all?
try

select top 10 * from
(
SELECT ilkid, reb,pts, firstname + N' ' + lastname AS name,
CONVERT(decimal(18, 1),
POWER(0.0 + pts + reb + asts + stl
+ blk + fgm - fga + ftm
- fta - turnover, 0.75) / 21) AS AV,
ROUND(POWER(((pts / CAST(gp AS numeric))
* (reb / CAST(gp AS numeric)))
* (asts / CAST(gp AS numeric)), 0.33),1) AS VI
FROM basketball.Player_regular_season
WHERE ([year] = 2003)
) a
ORDER BY VI DESC

==========================================
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.

AndyC
Yak Posting Veteran

53 Posts

Posted - 2005-02-15 : 15:26:43

Yes No data at all. In QA just the column headers get shown

BTW your code appeared to run much quicker - albeit
still producing no returns. Is that to be expected?
Again if I substitute reb for VI the data appears -
though not in the order I desire

Andrew Clark
www.majorleaguecharts.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources