时间戳

首页 > 解决方案 > MS Access SQL 子查询（计数）更改 IFF 逻辑结果的值

sql - MS Access SQL 子查询（计数）更改 IFF 逻辑结果的值

问题描述

微软访问 2016

我有以下查询作为例外工作。IIF 第一个条件 IsNull(caseDecision.completeFlag) 在 completedFlag 为空时解析为 true。

SELECT 
  client.*
  , IIf(IsNull(caseDecision.completeFlag),
    "",
    IIf(caseDecision.completeFlag=True,"YES","STARTED")) AS completeFlag
  , caseDecision.decisionNum
FROM client LEFT JOIN caseDecision ON client.kcas_key = caseDecision.kcas_key
WHERE caseDecision.decisionNum = 1
  OR caseDecision.decisionNum IS NULL
ORDER BY client.kcas_key DESC;

当我添加以下子查询时：

  , (SELECT COUNT(kcas_key) FROM caseDecision as cd WHERE cd.kcas_key = client.kcas_key) AS cnt

提到的 IIF(IsNull(caseDecision.completeFlag) 从来都不是真的。所以该子查询似乎正在影响 IIF。有什么想法吗？

这是带有子查询的 SQL

SELECT 
  client.*
  , IIf(IsNull(caseDecision.completeFlag),
    "",
    IIf(caseDecision.completeFlag=True,"YES","STARTED")) AS completeFlag
  , caseDecision.decisionNum
  , (SELECT COUNT(kcas_key) FROM caseDecision as cd WHERE cd.kcas_key = client.kcas_key) AS cnt
FROM client LEFT JOIN caseDecision ON client.kcas_key = caseDecision.kcas_key
WHERE caseDecision.decisionNum = 1
  OR caseDecision.decisionNum IS NULL
ORDER BY client.kcas_key DESC;

标签： sqlsubqueryms-access-2016

解决方案

使用nz函数如下：

IIf(nz(caseDecision.completeFlag, Flase)=True, "YES", "STARTED"))

推荐阅读