Decimal problems - 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)

Decimal problems

Author

Topic

Job
Yak Posting Veteran

69 Posts

Posted - 2005-08-12 : 12:33:49

I cannot get this to show decimals

Select top 100
Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)/15 +
TxnUnitsBase as decimal(10,2)),TxnUnitsDur,TxnUnitsBase,TxnUnits
From Tbl_Txn_UT

Where (TxnUnits like '%M%')

I thought just by doing something like A/B = 3.3333 would work. What would prohibit the decimals?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-12 : 12:57:55

Try .../15.0...
It's doing a intteger division, You loose precision.

rockmoose

Job
Yak Posting Veteran

69 Posts

Posted - 2005-08-12 : 13:01:49

quote:
Originally posted by rockmoose

Try .../15.0...
It's doing a intteger division, You loose precision.

rockmoose

When I change to /15.00 or /15.0 I get the error:
'Arithmetic overflow error converting numeric to data type numeric.'

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-12 : 13:36:26

[code]SELECT CAST(33/15 AS DECIMAL(9,2)) , CAST(33/15.0 AS DECIMAL(9,2))

----------- -----------
2.00 2.20[/code]

See the difference ?
The LEN function You are using returns an INT, so You are performing an Integer division.

rockmoose

Job
Yak Posting Veteran

69 Posts

Posted - 2005-08-12 : 13:44:10

Hmmm...maybe there is a better way to do it then.. the TxnUnitsDur is something like 200M or some number followed by an M. I'm doing the len to parse off that M. Is there a better way to do this so the desired calculation will work?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-12 : 14:04:53

What was I thinking talking about the LEN function, It is not really the problem.

Keep the LEFT & LEN function to parse out the number from the string.
Cast the parsed number to e.g. FLOAT and divide by 15

Try the examples:
SELECT CAST( LEFT('200M',LEN('200M')-1) AS FLOAT) / 15 ---< FLOAT / INT => FLOAT
SELECT CAST( LEFT('200M',LEN('200M')-1) AS INT) / 15 ---< INT / INT => INT

rockmoose

Job
Yak Posting Veteran

69 Posts

Posted - 2005-08-12 : 14:31:53

Thanks! It worked! I thought I had tried converting each part, but my syntax must have still been wrong.

Cheers!

Job

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-12 : 15:25:22

You're welcome.
Sorry for confusing You at first...

The errormessage bugged me out too

btw.

rockmoose

Job
Yak Posting Veteran

69 Posts

Posted - 2005-08-13 : 10:51:17

Hi again. Another problem has arisen. Here is the error along with the final version of the code I'm using:

'Invalid
length parameter passed to the substring function'

Update Tbl_Txn_UT
Set Fxd_Units =
CASE
WHEN TxnUnitsBase is Null and (TxnUnits like '%,%' or TxnUnits like
'%M%' and TxnUnitsDur like '%M%')
THEN Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)as Float)/15

WHEN TxnUnitsDur is Null and (TxnUnits like '%,%' or TxnUnits like
'%M%')
THEN TxnUnitsBase

WHEN (TxnUnits like '%,%' or TxnUnits like '%M%')
THEN Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)as Float)/15 +
TxnUnitsBase

Else TxnUnits

END
Where TxnUnits is not Null

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-13 : 11:04:52

You probably have a TxnUnitsDur that's just an empty string (length = 0)

Check:
SELECT * FROM Tbl_Txn_UT WHERE LEN(TxnUnitsDur) = 0

rockmoose

Job
Yak Posting Veteran

69 Posts

Posted - 2005-08-13 : 11:36:31

Ok, I'm running that now. Your probably right....I'll post back with results, so I just need to build then len=0 into the case statement.

Job
Yak Posting Veteran

69 Posts

Posted - 2005-08-14 : 11:04:20

There were a substantial number of lines where that was the case. Added the case statement and viola no errors.

Thanks again for all of your help.

Cheers,

Job

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-14 : 13:20:10

You're welcome

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources