Databases
StackOverflow2013
Postsdbo
POCase Statements using variables not working
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCase Statements using variables not working
    text
    copied!<p>EDIT: Made the query much simpler, same problem.</p> <p>I have basically two statements that select whether a person is deceased using a given account ID and filing period.</p> <p>When inputting an account with joint account holders, both queries (pr and jo) return values and the correct indicator shown in the case statement.</p> <p>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.</p> <p>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?</p> <p>Thanks!</p> <pre><code>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&lt;&gt;'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&lt;&gt;'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 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload