我正在使用我见过的最奇怪的整理数据库。我正在运行一个听起来很简单的案例语句。
代码:
SELECT CASE
WHEN PMA_DESC = ' ' THEN dbo.CAN_TBL.CAN_DESC
ELSE 'NO DESCRIPTION'
END AS PMA_DESC
, PMA_PART_ONLY
, PMA_ANAL_FIELD1
, CAN_DESC
, PMA_CREATED
, PMA_UPDATED
FROM dbo.PMA_TBL INNER JOIN
dbo.CAN_TBL ON dbo.PMA_TBL.PMA_ANAL_FIELD1 = dbo.CAN_TBL.CAN_CODE
WHERE (CAST(dbo.PMA_TBL.PMA_CREATED AS DATE) >= DATEADD(dd, - 3, CAST(GETDATE() AS DATE)) OR
CAST(dbo.PMA_TBL.PMA_UPDATED AS DATE) >= DATEADD(dd, - 3, CAST(GETDATE() AS DATE))) AND (dbo.PMA_TBL.PMA_DESC IS NOT NULL)
,尽管列中只有44个记录,但该案例语句的第一部分仍在字段中查找空间PMA_DESC
返回整列。因此,它正在找到该领域的任何空间,并将该领域的整个内容都作为积极的结果。自从我开始与SQL Server工作3 1/2年前,我从未见过。这与数据库整理有关吗?我知道这将打算键入强制案例灵敏度(是的)。
选择结果;
PMA_DESC PMA_PART_ONLY PMA_ANAL_FIELD1 CAN_DESC
NO DESCRIPTION 82240069 82 GLACIER MTM
NO DESCRIPTION 8228399 82 GLACIER MTM
NO DESCRIPTION 8235579 82 GLACIER MTM
第一列结果已经具有自己的各个部分描述,但是由于它们在单词之间包含一个空间,因此案例语句已将其转换为"没有描述"。那肯定不是吗?
[field] = ' '
如何将周围的文本视为正面的任何文本?
数据库整理设置为:sql_latin1_general_cp1_ci_as
这是我是个笨蛋,而不是正确地确定我的案例语句的逻辑。这会产生我需要的结果,因此我无法正确地与其他值一起使用空白。
SELECT CASE
WHEN PMA_DESC = ' ' AND CAN_DESC IN ('INVOICE ODDBALLS','SLOW MOVING') THEN 'NO DESCRIPTION'
WHEN PMA_DESC = ' ' AND CAN_DESC NOT IN ('INVOICE ODDBALLS','SLOW MOVING') THEN CAN_DESC
ELSE PMA_DESC
END AS PMA_DESC , PMA_PART_ONLY, PMA_ANAL_FIELD1, CAN_DESC, PMA_CREATED, PMA_UPDATED
FROM dbo.PMA_TBL INNER JOIN
dbo.CAN_TBL ON dbo.PMA_TBL.PMA_ANAL_FIELD1 = dbo.CAN_TBL.CAN_CODE
WHERE (CAST(dbo.PMA_TBL.PMA_CREATED AS DATE) >= DATEADD(dd, - 3, CAST(GETDATE() AS DATE)) OR
CAST(dbo.PMA_TBL.PMA_UPDATED AS DATE) >= DATEADD(dd, - 3, CAST(GETDATE() AS DATE))) AND (dbo.PMA_TBL.PMA_DESC IS NOT NULL)
感谢您抽出宝贵的时间阅读,下次我会更加努力!