Msg#: 4211977 posted 10:27 pm on Oct 5, 2010 (gmt 0)
We have to do Case in SQL homework. Never seen it before. The teacher wants returned values.
select orderitems.orderid, orderitems.partid, case when qty>10 then (orderitems.qty*inventory.price*.9) when qty>5 then (orderitems.qty*inventory.price*.95) else (orderitems.qty*inventory.price*1) end as Extended price
from orderitems, inventory, orders
group by orders.orderid
I get an error for inventory. price, which is a valid table value.
Msg#: 4211977 posted 10:37 pm on Oct 5, 2010 (gmt 0)
What error do you get?
One thing that jumps out at me is that you don't have any joining conditions for the tables. e.g. where orderitems.partid = inventory.partid But that should just mean you get a lot more rows than you were expecting.
Also you have table orders in the from clause but you aren't selecting any fields from it, you will probably want to add that to your select, and also have a joining clause.