'select' statement question again
table name = stk_tbl
prd_cde typ price
------- --- -----
P01 A 10
P02 A 10
P03 B 5
P04 B 5
P01 C 1
P02 C 1
we don't know how many prd_cde will present, we require add a group by statement,
and now what is the select statement to plus A AND B price
MINUS C prices and required group by prd_cde?
What is wrong of below SQL statement?
select sum(case when typ = 'C' then -pri else pri) from stk_tbl
but it come out an error statement("Function name is missing).").
Re: 'select' statement question again
Mango <firstname.lastname@example.org> wrote in message news:email@example.com...
> What is wrong of below SQL statement?
> select sum(case when typ = 'C' then -pri else pri) from stk_tbl
> but it come out an error statement("Function name is missing).").
You didn't say which database system you are using.
For SQL Server, the syntax for CASE is:
CASE typ WHEN 'C' THEN -pri ELSE pri END
I don't know if you can put that into a SUM, so you should try
a simpler SELECT statement first:
SELECT typ, CASE typ WHEN 'C' THEN -pri ELSE pri END
If that works then try:
SELECT SUM(CASE typ WHEN 'C' THEN -pri ELSE pri END)
That should get you the result you want, but you should probably
think about whether you need an additional table:
Hope that helps.
Impress your friends. Become an SQL guru.
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL