Using select queries in case statements -- sql server programming

Jacco Schalkwijk
8/3/2003 8:13:55 PM

Hi Luqman,

You can do it with a join:

SELECT sum(a.TAmt)
FROM acc b
INNER JOIN acc a
ON a.acc_code LIKE
CASE acc_dtl
WHEN 'Y' THEN b.acc_code
ELSE a.acc_code+'.%'
END

order by b.acc_sub,b.acc_code

[quoted text, click to view]

Luqman
8/3/2003 11:08:04 PM

I want to use the multiple case statements in following query instead of
select sub query, how can I do so?

Here is the query

Select
(case when acc_dtl='Y' then
(select sum(Acc_Amt) from Acc
where acc_code=B.acc_code)
else
(select sum(Acc_Amt) from Acc
where acc_code like B.acc_code+'.%')
end)
from acc b
order by acc_sub,acc_code

It should look something like this, I tried following query but its not
giving the results what above query is.

Select
(case when acc_dtl='Y' then
sum(case Acc_Code=B.Acc_code then TAmt else ) end)
else
sum(case Acc_Code like B,Acc_code+'.%' then TAmt else 0 end)
end)
from acc b
order by acc_sub,acc_code

I want to change the query bcoz I want to use this query in sql server ce
which does not support select sub queries in case statements.

Best Regards,

Luqman

sql server programming : Using select queries in case statements