Update with different CAST and CONVERT

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

Update with different CAST and CONVERT

Author

Topic

bisjom
Starting Member

4 Posts

Posted - 2014-07-06 : 21:15:48

Hi
I have a table with all varchar data for all different fields for money and int etc. I want to do a calcuation in one field with the rule as below:
UPDATE [dbo].[tblPayments]
SET [PreInjuryWage]=
CASE
WHEN [WeekNo]<14
THEN ([MaxWorkCover]+ [WeeklyEarnings] )/0.95
ELSE ([MaxWorkCover] + 0.80 * [WeeklyEarnings] ) /0.80
END

I tried this command after many other ways of tries


UPDATE [dbo].[tblPayments1]
SET [PreInjuryWage]= 
CASE  
WHEN  [WeekNo]<14 
THEN  CAST ( (cast([MaxWorkCover] as money  )+ cast([WeeklyEarnings] as money  ))/0.95   AS Varchar(10) )
ELSE CAST ( (convert(money,  [MaxWorkCover] + (0.80 * convert(money, [WeeklyEarnings] )) )/0.80 )  as varchar(10))
END

The structure of the table ( its a legacy table from 2000 i beleive)


CREATE TABLE [dbo].[tblPayments](
	[PaymentID] [int] IDENTITY(1,1) NOT NULL,
	[ClaimID] [int] NOT NULL,
	[WeekNo] [smallint] NULL,
	[PayPeriod] [varchar](10) NULL,
	[StartDate] [varchar](10) NULL,
	[EndDate] [varchar](10) NULL,
	[MaxWorkCover] [varchar](10) NULL,
	[HourlyRate] [varchar](10) NULL,
	[HoursWorked] [varchar](10) NULL,
	[Loading] [varchar](10) NULL,
	[WeeklyEarnings] [varchar](10) NULL,
	[Payment] [varchar](10) NULL,
	[UnderThreshold] [varchar](10) NULL,
	[OverThreshold] [varchar](10) NULL,
	[MakeUpPay] [varchar](10) NULL,
	[Reimbursement] [varchar](10) NULL,
	[ReimbComments] [varchar](255) NULL,
	[PreInjuryWage] [varchar](10) NULL,
	[AwardWage] [varchar](10) NULL,
 CONSTRAINT [PK__tblPayments__4316F928] PRIMARY KEY CLUSTERED 
(
	[PaymentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Can you help me to solve this??

I am getting the error:
Msg 8115, Level 16, State 5, Line 1
Arithmetic overflow error converting numeric to data type varchar.

yado
Starting Member

8 Posts

Posted - 2014-07-07 : 03:35:39

unspammed

gvram
Starting Member

2 Posts

Posted - 2014-07-07 : 03:53:54

For your requirement, you no need convert into varchar datatype. See my below findings, Hope this will work for your requirement.

UPDATE tblPayments
SET [PreInjuryWage]=(
CASE WHEN[WeekNo] < 14
THEN (cast([MaxWorkCover] as int)+ cast([WeeklyEarnings] as int))/0.95
ELSE (CAST([MaxWorkCover] AS INT ) + 0.80 * CAST([WeeklyEarnings] AS INT))/0.80
end )

Venkat G

bisjom
Starting Member

4 Posts

Posted - 2014-07-07 : 04:04:31

quote:
Originally posted by gvram

Hope this will work for your requirement

UPDATE tblPayments
SET [PreInjuryWage]=(
CASE WHEN[WeekNo] < 14
THEN (cast([MaxWorkCover] as int)+ cast([WeeklyEarnings] as int))/0.95
ELSE (CAST([MaxWorkCover] AS INT ) + 0.80 * CAST([WeeklyEarnings] AS INT))/0.80
end )

Venkat G

Hi Venkat,
I tried this but getting this error
Msg 8115, Level 16, State 5, Line 1
Arithmetic overflow error converting numeric to data type varchar.
The statement has been terminated.

Must be bacause of the result, exceeding varchar(10), i tried ROUND() but no luck!

BJ

gvram
Starting Member

2 Posts

Posted - 2014-07-07 : 04:38:40

In that case, after calculating the value what ever result you getting, that is exceeding the target column data type size.
You can use LEFT function to capture the first 10 digits from the result. Use the below query with LEFT function.

UPDATE tblPayments
SET [PreInjuryWage]=(
CASE WHEN[WeekNo] < 14
THEN LEFT((cast([MaxWorkCover] as int)+ cast([WeeklyEarnings] as int))/0.95,10)
ELSE LEFT((CAST([MaxWorkCover] AS int) + 0.80 * CAST([WeeklyEarnings] AS int))/0.80,10)
end )

Venkat G

bisjom
Starting Member

4 Posts

Posted - 2014-07-07 : 07:10:54

quote:
Originally posted by gvram

In that case, after calculating the value what ever result you getting, that is exceeding the target column data type size.
You can use LEFT function to capture the first 10 digits from the result. Use the below query with LEFT function.

UPDATE tblPayments
SET [PreInjuryWage]=(
CASE WHEN[WeekNo] < 14
THEN LEFT((cast([MaxWorkCover] as int)+ cast([WeeklyEarnings] as int))/0.95,10)
ELSE LEFT((CAST([MaxWorkCover] AS int) + 0.80 * CAST([WeeklyEarnings] AS int))/0.80,10)
end )

Venkat G

tried this and worked..
UPDATE [dbo].[tblPayments]
SET [PreInjuryWage] =
CONVERT(NUMERIC(19, 2),
CASE
WHEN [WeekNo] < 14
THEN (CAST([MaxWorkCover] AS MONEY) + CAST([WeeklyEarnings] AS MONEY)) / 0.95
ELSE (CAST([MaxWorkCover] AS MONEY) + (0.80 * CAST([WeeklyEarnings] AS MONEY))) / 0.80
END
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-07 : 11:22:06

[code]UPDATE dbo.tblPayments1
SET PreInjuryWage = CASE
WHEN WeekNo < 14 AND ISNUMERIC(MaxWorkCover) = 1 AND ISNUMERIC(WeeklyEarnings) = 1 THEN (CAST(MaxWorkCover AS MONEY) + CAST(WeeklyEarnings AS MONEY)) / 0.95
WHEN WeekNo >= 14 AND ISNUMERIC(MaxWorkCover) = 1 AND ISNUMERIC(WeeklyEarnings) = 1 THEN (CAST(MaxWorkCover AS MONEY) + 0.80 * CAST(WeeklyEarnings] AS MONEY)) / 0.80
ELSE 'Error'
END;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources