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

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

CodePudding user response：

As far as I can tell, your ELSE shouldn't be there. For the values you have, your ELSE would only be returned if P.AverageAnnualSalary is negative (unlikely, I assume you don't have staff that pay to work for you), NULL (pointless to then return it, might as well omit the ELSE), or between 199999 and 200000 (for example 199999.7) or between 99999 and 100000 (for example 99999.5).

You don't actually need the upper boundaries. For example if the first WHEN isn't true then the value is already implicitly less than 200000. Then you remove the ELSE completely, thus removing the implicit conversion.

    CASE WHEN P.AverageAnnualSalary >= 200000 THEN 'wow'
         WHEN P.AverageAnnualSalary >= 100000 THEN 'nice'
         WHEN P.AverageAnnualSalary >= 0 THEN 'ok'
    END

CodePudding user response：

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

CodePudding user response：

The result of a CASE expression is a single specific data type. SQL Server returns the "highest precedence type from the set of types in result_expressions"

Using data type precedence order you get "ok" and "nice" being converted to money

To solve this

ELSE CAST( P.AverageAnnualSalary AS VARCHAR( 30 ))