How Do I Convert VARCHAR to NUMERIC

Question

Please your assistance is needed on the query below.

CREATE TABLE GenReport(
	Category varchar (3) NULL,
	TotalCommission Decimal (19,4) NULL,
	EarnedCommission Decimal (19,4) null

)

INSERT INTO GenReport (Category, TotalCommission, EarnedCommission)
VALUES ('001', 3600.0000, 3600.0000 );
INSERT INTO GenReport (Category, TotalCommission, EarnedCommission)
VALUES ('001', 564827.1800, 564827.1800 );
INSERT INTO GenReport (Category, TotalCommission, EarnedCommission)
VALUES ('001', 2700.0000, 2700.0000 );
INSERT INTO GenReport (Category, TotalCommission, EarnedCommission)
VALUES ('001', 4700.0000, 4700.0000 );
INSERT INTO GenReport (Category, TotalCommission, EarnedCommission)
VALUES ('001', 6800.0000, 6800.0000 );


DECLARE @Category VARCHAR (3)
SET @Category='001'
SELECT CASE WHEN Category=@Category 
	THEN CONCAT(TotalCommission , '*disc') 
	ELSE TotalCommission END AS TotalCommission    
FROM GenReport 

My TotalCommission output result should look like below.

The error message is stated below

Msg 8114, Level 16, State 5, Line 110
Error converting data type varchar to numeric.

Thank you for your anticipated quick response.

Best regards,

Abiodunajai

Answer 1

DECLARE @Category VARCHAR (3)
SET @Category='001'
SELECT CASE WHEN Category=@Category 
	THEN CONCAT(TotalCommission , '*disc') 
	ELSE CONCAT(TotalCommission , '')  END AS TotalCommission    
FROM GenReport 

Answer 2

Try the following:

DECLARE @Category VARCHAR (3)
SET @Category='001'
SELECT CASE WHEN Category=@Category 
	THEN CONCAT(TotalCommission , '*disc') 
	ELSE CAST(TotalCommission AS VARCHAR(15)) END AS TotalCommission    
FROM GenReport