Welcome to WebmasterWorld Guest from 54.161.255.61

Forum Moderators: open

using case to calculate a return value

homework help

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

5+ Year Member



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!
10:37 pm on Oct 5, 2010 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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)

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



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

Featured Threads

Hot Threads This Week

Hot Threads This Month