使用变量的案例陈述不起作用 [英] Case Statements using variables not working

问题描述

使查询变得更加简单，相同的问题.

Made the query much simpler, same problem.

我基本上有两条语句可以使用给定的帐户ID和申请期限来选择某人是否已故.

I have basically two statements that select whether a person is deceased using a given account ID and filing period.

在输入具有联合帐户持有人的帐户时，两个查询(pr和jo)都返回值和case语句中显示的正确指示符.

When inputting an account with joint account holders, both queries (pr and jo) return values and the correct indicator shown in the case statement.

当第二个查询不返回任何值时(因为没有联合帐户持有人要被表明是活着的还是已故)，那么case语句似乎不起作用，也不返回任何值.

When the second query returns no values (because there is no joint account holder to be indicated alive or deceased) then the case statement doesn't seem to work and returns no value.

为什么会这样，即使第二张表不返回值，我如何使case语句仍返回值?

Why is this happening, and how can I get the case statement to still return a value even when the second table won't return a value?

谢谢！

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

推荐答案

由于您的最后一条评论说:"pr始终填充.jo有时不填充" ...
这意味着您需要在两者之间使用LEFT OUTER JOIN(可选联接).
这需要使JOIN明确:您必须有一个ON子句，准确说明哪些列被等价/比较.

Since your last comment says "the pr is always populated. the jo is sometimes not"...
that means you need a LEFT OUTER JOIN (optional join) between the two...
which requires making your JOINs explicit: you'll have to have an ON clause, saying exactly which columns are equated/compared.

然后，您还需要在CASE语句中放入IS NULL的支票，以查找未找到联名帐户的情况.

Then, you'll also need to put a check for IS NULL in your CASE statement, for when no joint accounts are found.

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的底部...我不知道两个SELECT之间的customerkey和accountkey通常是否匹配.)

(You might need to change this ON clause, at the bottom of the LEFT OUTER JOIN above... I didn't know if both your customerkey and accountkey generally match, between the 2 SELECTs.)

希望有帮助！

这篇关于使用变量的案例陈述不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！