为什么此查询失败并显示"Cannot convert a char value to money. "

Question

附录：我不知道为什么会有票数接近，因为"结果是不可重复的"提供了模式和数据脚本，并提供了有问题的脚本。一切都在那里。

此查询失败：

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

出现此错误：

消息235，级别16，状态0，第1行
无法将字符值转换为货币。char值的语法不正确。

此查询有效：

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

这是模式和数据：

/****** 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

Answer 1

据我所知，您的ELSE不应该在那里。对于您所拥有的值，只有当P.AverageAnnualSalary为负(不太可能，我假设您没有为您工作的员工(、NULL(然后返回它毫无意义，还可以省略ELSE(、或199999和200000之间(例如199999.7(或99999和100000之间(例如，99999.5(时，才会返回ELSE。

你实际上并不需要上限。例如，如果第一个WHEN不为真，则该值已隐式地小于200000。然后完全删除ELSE，从而删除隐式转换。

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

Answer 2

问题简单明了，在case语句中，您将返回除else情况外的charecter值。所以只要把其他的作为字符串，它就会起作用

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

Answer 3

CASE表达式的结果是一个特定的数据类型。SQL Server返回"；result_expression中的类型集合中的最高优先级类型"；

使用数据类型的优先顺序；ok"；以及"；漂亮的"；转换为money

为了解决这个

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