using case to calculate a return value

Forum Moderators: open

Message Too Old, No Replies

using case to calculate a return value

homework help

Baruch Menachem

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

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

10:54 pm on Oct 5, 2010 (gmt 0)

end as Extended price

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.)