Case Statements using variables not working

Question

EDIT: Made the query much simpler, same problem.

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

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

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.

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?

Thanks!

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

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.

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

(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.)

Hope that helps!