使用变量不工作case语句(Case Statements using variables not

编辑：作出查询简单得多，同样的问题。

我有两种基本语句选择使用一个给定的帐户ID和申报期一个人是否已经死亡。

当输入与联合帐户持有人帐户，这两个查询（PR和JO）返回值和正确的指示器的情况下说明所示。

当第二查询没有返回值（因为要指示活着还是死去没有联名帐户持有人），那么case语句似乎并没有工作，没有返回值。

为什么会出现这种情况，我怎么能得到case语句仍返回一个值，即使第二个表将不会返回一个值？

谢谢！

SELECT 
CASE
            WHEN    pr.fintPriDeceased=0 and (jo.fintJointDeceased=0 or jo.fintJointDeceased='')
            THEN    0
            ELSE    1
            END AS fintDeceased
FROM

(SELECT a.FLNGCUSTOMERKEY as flngPrimaryCustomerKey,
        a.flngAccountKey as flngPrimaryAccountKey,
        CASE
            WHEN ci.fdtmCease<>'12-31-9999' 
            THEN    1
            ELSE    0
            END AS fintPriDeceased
FROM    tblAccount a,
        tblPeriod p,
        tblCustomerInfo ci
WHERE   a.flngAccountKey    = @plngAccountKey and
        p.fdtmFilingPeriod  = @pdtmFilingPeriod and
        a.flngAccountKey    = p.flngAccountKey and
        a.FLNGCUSTOMERKEY   = ci.flngCustomerKey) pr

(SELECT a.FLNGCUSTOMERKEY as flngJointCustomerKey,
        p.FLNGACCOUNTKEY as flngJointAccountKey,
        CASE
            WHEN ci.fdtmCease<>'12-31-9999' 
            THEN    1
            ELSE    0
            END AS fintJointDeceased
FROM    tblAccount a,
        tblPeriod p,
        tblCustomerInfo ci
WHERE   p.FLNGJOINTACCOUNTKEY   = @plngAccountKey and
        p.fdtmFilingPeriod      = @pdtmFilingPeriod and
        a.flngAccountKey        = p.flngAccountKey and
        a.FLNGCUSTOMERKEY       = ci.flngCustomerKey) jo

Answer 1:

由于您的最后评论说，“公关总是被填充，联办是有时不” ......
这意味着你需要一个LEFT OUTER JOIN两者（可选加入）...
这需要让你的JOIN明确的：你必须有一个ON子句，准确说这列等同/比较。

然后，你也需要把支票IS NULL在您的CASE语句，当发现没有联名账户的。

SELECT 
CASE
            WHEN    pr.fintPriDeceased=0 
            and     (jo.fintJointDeceased IS NULL 
                    OR jo.fintJointDeceased=0)
            THEN    0
            ELSE    1
            END AS fintDeceased

FROM
(SELECT a.FLNGCUSTOMERKEY as flngPrimaryCustomerKey,
        a.flngAccountKey as flngPrimaryAccountKey,
        CASE
            WHEN ci.fdtmCease<>'12-31-9999' 
            THEN    1
            ELSE    0
            END AS fintPriDeceased
FROM    tblAccount a,
        tblPeriod p,
        tblCustomerInfo ci
WHERE   a.flngAccountKey    = @plngAccountKey and
        p.fdtmFilingPeriod  = @pdtmFilingPeriod and
        a.flngAccountKey    = p.flngAccountKey and
        a.FLNGCUSTOMERKEY   = ci.flngCustomerKey) pr

LEFT OUTER JOIN 
(SELECT a.FLNGCUSTOMERKEY as flngJointCustomerKey,
        p.FLNGACCOUNTKEY as flngJointAccountKey,
        CASE
            WHEN ci.fdtmCease<>'12-31-9999' 
            THEN    1
            ELSE    0
            END AS fintJointDeceased
FROM    tblAccount a,
        tblPeriod p,
        tblCustomerInfo ci
WHERE   p.FLNGJOINTACCOUNTKEY   = @plngAccountKey and
        p.fdtmFilingPeriod      = @pdtmFilingPeriod and
        a.flngAccountKey        = p.flngAccountKey and
        a.FLNGCUSTOMERKEY       = ci.flngCustomerKey) jo
ON pr.flngPrimaryCustomerKey = jo.flngJointCustomerKey
AND pr.flngPrimaryAccountKey = jo.flngJointAccountKey

（您可能需要改变这种ON子句，在LEFT OUTER底部JOIN以上......我不知道，如果你都和customerkey一般accountkey匹配，在2个选择之间。）

希望帮助！

文章来源: Case Statements using variables not working