Why does this query fail with "Cannot convert a char value to money. "

December 14, 2021

Addendum: I don’t have a clue as to why this has close votes because "the results are not reproducible." The schema and data script is provided and the script in question is provided. It’s all there.

This query fails:

SELECT 
    P.Profession AS Profession,
    CASE
        WHEN P.AverageAnnualSalary >= 200000
            THEN 'wow'
        WHEN P.AverageAnnualSalary >= 100000 
             AND P.AverageAnnualSalary <= 199999
            THEN 'nice'
        WHEN P.AverageAnnualSalary >= 0 
             AND P.AverageAnnualSalary <= 99999
            THEN 'ok'
        ELSE P.AverageAnnualSalary
    END AS Description
FROM
    tProfession2 AS P
ORDER BY 
    Profession ASC

with this error:

Msg 235, Level 16, State 0, Line 1
Cannot convert a char value to money. The char value has incorrect syntax.

This query works:

SELECT Profession AS Profession,
       CASE 
           WHEN P.AverageAnnualSalary >= 200000 THEN 'wow'
           WHEN P.AverageAnnualSalary >= 100000 THEN 'nice'
           ELSE 'ok'
        END AS Description
FROM tProfession2 P
ORDER BY Profession ASC

This is the schema and data:

/****** Object:  Table [dbo].[tProfession2]    Script Date: 12/14/2021 10:48:41 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tProfession2](
    [ProfessionID] [int] IDENTITY(1,1) NOT NULL,
    [Profession] [nchar](50) NOT NULL,
    [AverageAnnualSalary] [money] NULL,
 CONSTRAINT [PK_tProfession2] PRIMARY KEY CLUSTERED 
(
    [ProfessionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tProfession2] ON 
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (1, N'Doctor                                            ', 200000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (2, N'Attorney                                          ', 350000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (3, N'Software Engineer                                 ', 120000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (4, N'Cybersecurity Consultant                          ', 110000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (5, N'Network Engineer                                  ', 111000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (6, N'Farmer                                            ', 200000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (7, N'Chef                                              ', 250000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (8, N'Truck Driver                                      ', 99000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (9, N'Wilderness Guide                                  ', 45000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (10, N'HVAC Technician                                   ', 79000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (11, N'Electrician                                       ', 80000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (12, N'TV Personality                                    ', 450000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (13, N'NFL Quarterback                                   ', 2000000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (14, N'Soccer Player                                     ', 4000000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (15, N'Electrical Engineer                               ', 130000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (16, N'Computer Engineer                                 ', 135000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (17, N'Business Owner                                    ', 4500000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (18, N'Politician                                        ', 20000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (19, N'City Manager                                      ', 90000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (20, N'Landscaper                                        ', 80000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (21, N'Pilot                                             ', 200000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (22, N'Insurance Salesperson                             ', 230000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (23, N'Interior Designer                                 ', 55000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (24, N'Architect                                         ', 600000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (25, N'IRS Agent                                         ', 99000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (26, N'Accountant                                        ', 120000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (27, N'Tax Preparer                                      ', 60000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (28, N'Spy                                               ', 0.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (29, N'Military Officer                                  ', 45000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (30, N'Veterinarian                                      ', 300000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (31, N'Roofer                                            ', 67000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (32, N'Arborist                                          ', 76000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (33, N'Painter                                           ', 60000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (34, N'Flight Attendant                                  ', 50000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (35, N'Amish Farmer                                      ', 10000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (36, N'Carpet Installer                                  ', 40000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (37, N'Baker                                             ', 900000.0000)
GO
SET IDENTITY_INSERT [dbo].[tProfession2] OFF
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [IX_tProfession]    Script Date: 12/14/2021 10:48:42 AM ******/
ALTER TABLE [dbo].[tProfession2] ADD  CONSTRAINT [IX_tProfession2] UNIQUE NONCLUSTERED 
(
    [Profession] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

>Solution :

The issue is simple and straight, in the case statement you are returning the charecter values excpet for the else case. so Just cast the else as string and it’ll work

SELECT P.Profession AS Profession,
    CASE
        WHEN P.AverageAnnualSalary >= 200000
            THEN 'wow'
        WHEN P.AverageAnnualSalary >= 100000 
             AND P.AverageAnnualSalary <= 199999
            THEN 'nice'
        WHEN P.AverageAnnualSalary >= 0 
             AND P.AverageAnnualSalary <= 99999
            THEN 'ok'
        ELSE CAST(P.AverageAnnualSalary AS VARCHAR(50))
    END AS Description
FROM tProfession2 AS P
ORDER BY Profession ASC