Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

using case to calculate a return value

homework help

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

Junior Member

10+ Year Member

joined:Mar 26, 2008
posts: 127
votes: 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.

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

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
votes: 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.
10:54 pm on Oct 5, 2010 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
votes: 109

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