homepage Welcome to WebmasterWorld Guest from 54.196.195.158
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
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)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved