Problem in complex CASE IF function

[prodigymunky]

Here's the SQL command I'm trying to impliment ...


SELECT DISTINCT

t.ID AS ID,
t.topTitle AS topTitle,
t.topDescription AS topDescription,
t.topCategory AS topCategory,
t.topOrder AS topOrder,

(SELECT
COUNT(posTopic)
FROM posts
WHERE posTopic = t.ID
AND posParent = 0
)
AS topPosts,

(SELECT
COUNT(posTopic)
FROM posts
WHERE posTopic = t.ID
AND posParent <> 0
)
AS topReplies,

(SELECT
MAX(posTimestamp)
FROM posts
WHERE posTopic = t.ID
)
AS topRecentTimestamp,

( CASE WHEN ( SELECT MAX(posTimestamp) FROM posts WHERE posTopic = t.ID )
IS NULL THEN 0
ELSE ( ( SELECT posAuthor FROM posts WHERE posTopic = t.ID AND posTimestamp = ( SELECT MAX(posTimestamp) FROM posts WHERE posTopic = t.ID ) ) )
END )
AS topRecentAuthor

FROM
topics t,
posts p

WHERE
t.topCategory = 1

ORDER BY
t.topOrder


Everything works fine except for the CASE command. The individual SELECT commands inside the CASE command work fine, but when I put them all together into the CASE command, it fails. Does anyone know why?

[chazzy]

is it giving you an error or is it not returning any results?

[prodigymunky]

The error I'm getting is the usual one ...
"IErrorInfo.GetDescription failed with E_FAIL(0x80004005)."

Here's some other error information ... not much of help probably, but it's also at http://elvenbelief.brinkster.net/test.aspx



[OleDbException (0x80004005): IErrorInfo.GetDescription failed with E_FAIL(0x80004005).]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +174
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +65
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +112
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +68
System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +5
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +36
ASP.test_aspx.Page_Load(Object sender, EventArgs e) in \\premfs7\sites\premium2\elvenbelief\webroot\test.aspx:25
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +750

[chazzy]

the first thing that jumps out to me is that you don't have a default case. not sure if it'll matter on something like this though.