'float' to 'numeric' problem!!

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

General SQL Server Forums

New to SQL Server Programming

'float' to 'numeric' problem!!

Author

Topic

Stubert
Starting Member

10 Posts

Posted - 2011-04-06 : 15:47:09

Guys,

I've looked online for hours now and cannot find a definitive answer.
I have a table which I am writing a query for SELECT, CASE, WHERE, ELSE type stuff.

In one of my CASES, I wish to add up a few columns and compare the result with another calculation.

The problem is that one column is "float" whereas all the others are "numeric" (18,2). The arithmetic statement does not work (error converting to integer).
If I use CONVERT(numeric[column_name) in the statement, it gets rid of the error, but ignores the value in evaluating the statement.

What am I doing wrong? Thanks..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-06 : 15:49:05

You'll need to post the query, show us some sample data plus expected results, and highlight for us where it's going wrong.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Stubert
Starting Member

10 Posts

Posted - 2011-04-07 : 04:30:06

OK, this is the relevant case

WHEN POLICY_NR is not NULL AND [SAP Total] = [MART Total] AND (([760151] <> [M760151]) OR ([760152] <> [M760152])
OR ([760171] <> [M760171]) OR ([760172] <> [M760172]) OR ([760155] <> [M760155])
OR ([760156] <> [M760156]) OR ([760157] <> [M760157]) OR ([760158] <> [M760158]))

AND (CONVERT(Numeric,[KTOM_Total]))-[760151]+[760152]+[760171]+[760172]=[M760151]+[M760152]+[M760171] + [M760172]
THEN 'Policy Matches after KTOM adjustment'

The bolded bit is where I'm having trouble. KTOM_Total is 'float' in the underlying table, so I thought using CONVERT(Numeric, [KTOM_Total]) would allow it to be used in a the calculation.
It doesn't give an error, just doesn't pick up the value (ignores it).

many thanks.

Stubert
Starting Member

10 Posts

Posted - 2011-04-07 : 04:40:00

Sample code

Allocation	Policy_Number	POLICY_NR	760151	760152	760171	760172	760153	760155	760156	760157	760158	760167	681473	REALLOCATE	AIE001660	M760151	M760152	M760171	M760172	M760153	M760155	M760156	M760157	M760158	M760167	M681473	K760151	K760152	K760171	K760172	K760153	K760155	K760156	K760157	K760158	K760167	K681473	KTOM_Total	SAP_Equals_MART	SAP Total	MART Total	SAP v MART Diff	Last_Posting_Date	New_Date	Segment
RKJ456078	NULL	RKJ456078	0.00	0.00	0.00	-348.24	-288.58	0.00	348.24	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0	0	0	0	0	0	-348.24	0	0	0	0	-348.24	YES	0.00	0.00	0.00	2011-01-05	2011-01-05	KTOM adjustment will not correct the policy

In this example,

(-348.24)-0.00+0.00+0.00+(-348.24)= 0.00+0.00+0.00+0.00 (the code formatting in html doesn't show it though!!)

ie the condition is true (to my eyes), yet the code interprets it as false (as though the first value is not there).

Stubert
Starting Member

10 Posts

Posted - 2011-04-07 : 05:04:20

Solved it! rather than minusing a minus number, I changed the sign (within brackets) and added it instead

AND (-[KTOM_Total])+[760151]+[760152]+[760171]+[760172]=[M760151]+[M760152]+[M760171] + [M760172]

Amazing what a good nights sleep makes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources