SQL字符串转Decimal后创建视图返回空结果问题求助

可能的原因 #

• 关联查询无匹配数据：视图使用INNER JOIN关联两张表，若h.Asset = t.assets无任何匹配记录，视图自然返回空结果。
• 字符串与数值直接比较逻辑错误：CASE判断中直接用字符串类型的VibrationArms和数值（如220）做比较，属于字典序比较而非数值比较，会导致判断逻辑完全偏离预期。比如字符串'30'字典序大于'220'，但数值上30远小于220；字符串'1000'字典序小于'220'，但数值上1000远大于220。
• CAST转换异常隐性影响：若VibrationArms等列包含非数字字符（空格、字母、特殊符号），CAST转换会直接报错，但你能查询到空结果而非报错，说明要么无转换失败记录，要么转换失败返回NULL但未触发过滤。

解决步骤 #

1. 验证基础关联查询是否有数据 #

先执行以下查询确认两张表的关联条件是否有匹配记录：

SELECT COUNT(*) 
FROM dbo.oneyeardata1hr AS t 
INNER JOIN dbo.asset_hierarchy_new AS h 
ON h.Asset = t.assets;

如果结果为0，说明JOIN条件无匹配，可改用LEFT JOIN排查不匹配的记录：

SELECT t.*, h.*
FROM dbo.oneyeardata1hr AS t 
LEFT JOIN dbo.asset_hierarchy_new AS h 
ON h.Asset = t.assets;

检查Asset和assets列的数据是否存在大小写、空格、数据类型差异，修正关联条件。

2. 修正CASE判断的类型比较错误 #

所有数值比较必须使用转换后的decimal列（或已定义的别名），而非原字符串列。比如将：

(CASE WHEN VibrationArms >= 220 THEN 1 ELSE 0 END) AS Vibration_Arms_Alert

改为：

(CASE WHEN Vibration_Arms >= 220 THEN 1 ELSE 0 END) AS Vibration_Arms_Alert

同时注意修正备注文本的数值错误（比如告警备注写"超过250"但条件是>=220，需同步调整描述）。

3. 处理CAST转换的异常情况 #

用TRY_CAST替代CAST，转换失败时返回NULL而非报错，避免视图查询中断：

TRY_CAST(VibrationArms AS decimal) AS Vibration_Arms

同时排查无法转换的异常记录：

SELECT VibrationArms
FROM dbo.oneyeardata1hr
WHERE TRY_CAST(VibrationArms AS decimal) IS NULL;

对这些记录进行清洗（去除无效字符、标记无效数据）。

4. 重新创建视图并验证 #

修正所有问题后，重新创建视图并查询验证：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER VIEW [dbo].[2yrAlerts_and_Warnings]
AS
SELECT        
h.region, h.plant, t.assets, t.Types, t.machineID, 
TRY_CAST(VibrationArms AS decimal) AS Vibration_Arms,
TRY_CAST(VibrationVrms AS decimal) AS Vibration_Vrms,
TRY_CAST(VibrationPeak AS decimal) AS Vibration_Peak,
TRY_CAST(VibrationDKW AS decimal) AS Vibration_DKW,
TRY_CAST(PerformanceCurrent AS decimal) AS Performance_Current,
TRY_CAST(PerformanceSpeed AS decimal) AS Performance_Speed,
TRY_CAST(PerformancePower AS decimal) AS Performance_Power,
TRY_CAST(StatEnergy AS decimal) AS Stat_Energy,

t.VibrationArms,
220 AS Vibration_Arms_Threshold,
(CASE WHEN Vibration_Arms >= 220 THEN 1 ELSE 0 END) AS Vibration_Arms_Alert,
(CASE WHEN Vibration_Arms >= 220 THEN 'There is an alert because value of Arms output of vibration sensor is more than 220 ' ELSE 'No Alerts' END) AS Vibration_Arms_Alert_Remarks,
(CASE WHEN Vibration_Arms BETWEEN 210 AND 220 THEN 1 ELSE 0 END) AS Vibration_Arms_Warning,
(CASE WHEN Vibration_Arms BETWEEN 210 AND 220 THEN 'There is a warning because value of Arms output of vibration sensor lies between 210 and 220 ' ELSE 'No Warnings' END) AS Vibration_Arms_Warning_Remarks,
(CASE WHEN Vibration_Arms > 230 THEN 1 ELSE 0 END) AS CRITICAL_ALERT_Vibration_Arms,

t.timess, 
DATEPART(year, t.timess) AS Year, 
DATEPART(Month, t.timess) AS Month, 
DATEPART(WEEK, t.timess) AS Week, 
DATEPART(Day, t.timess) AS Day, 
DATEPART(Hour, t.timess) AS Hour
FROM dbo.oneyeardata1hr AS t 
INNER JOIN dbo.asset_hierarchy_new AS h 
ON h.Asset = t.assets;
GO

-- 验证视图结果
SELECT * FROM [dbo].[2yrAlerts_and_Warnings];

内容的提问来源于stack exchange，提问作者PeakyBlinder