| using case to calculate a return value
homework help |
Baruch Menachem
msg:4211979 | 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.
Thanks!
|
Dijkgraaf
msg:4211982 | 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.
|
LifeinAsia
msg:4211985 | 10:54 pm on Oct 5, 2010 (gmt 0) |
I see this part as an issue- space in the name.
Another issue is that if qty>10, then both the 1st (qty>10) and 2nd (qty>5) evaluate as true. Not sure if it's allowed in your DB, but it's not good programming.
| I get an error for inventory. price, which is a valid table value. |
|
What about NULL values?
And I agree with Dijkgraaf- knowing the exact error message would help.
(Also note that this is not the place to get your homework assignments done.)
|
|
|
