I’m seeing a couple of issues here.

First, I don’t see you closing your case statements.

<pre>SELECT DISTINCT …
CASE WHEN balcode = ‘ACTUAL’ AND yr = 2008 AND period = 6 THEN
full_value
END AS Act_Amt,
…</pre>

Now if the full_value has a NULL value in it, it’s a little easier to work with. You can use the IS_NULL function to see if it’s NULL and replace the value if it is. That would turn the above code into

<pre>SELECT DISTINCT …
CASE WHEN balcode = ‘ACTUAL’ AND yr = 2008 AND period = 6 THEN
IS_NULL(full_value, 0)
END AS Act_Amt,
…</pre>

Now if the value is blank, instead of NULL you need to use another CASE statement within the first case statement like this.

<pre>SELECT DISTINCT …
CASE WHEN balcode = ‘ACTUAL’ AND yr = 2008 AND period = 6 THEN
CASE WHEN full_value <> ” THEN full_value ELSE 0 END
END AS Act_Amt,
…</pre>

Now, it appears that all your data comes from a single table so you need to do a cross tab query. If you have SQL 2005 you can use the PIVOT function to swing the data around. If you have SQL 2000 or below you will need to do it the hard way.

<pre>SELECT DISTINCT oas_balance.cmpcode, oas_balance.el1, oas_balance.el2, oas_balance.full_value,
MAX(CASE WHEN balcode = ‘ACTUAL’ AND yr = 2008 AND period = 6 THEN
CASE WHEN full_value <> ” THEN full_value ELSE 0 END
END) AS Act_Amt,
…
FROM oas_balance
WHERE …
GROUP BY oas_balance.cmpcode, oas_balance.el1, oas_balance.el2, oas_balance.full_value
</pre>