why's my SQL statement invalid?

[Desolator144]

I'm using this statement on the wonderful DB2 (on an AS400 ) and it says that the calculated expression is invalid so what's the correct way to write this:

select order_num, SUM(num_ordered) * quoted_price as order_total
from order_line order by order_num

I tried it with parenthesis around the whole thing and without the as statement and neither way worked either

[Pradeep1210]

I think you should create a Function which does the calculation and use that function in the Query.


Pradeep

[Desolator144]

me too but the assignment says not to

[si_the_geek]

To get the Sum you need to use a Group By, or a sub-query.

In this case a Group By is not valid (as you are using the result in a calculation), so here's how you would do the sub-query:

select order_num, (SELECT SUM(num_ordered) FROM order_line) * quoted_price as order_total
from order_line order by order_num

[Desolator144]

well I ran that one and apparently it can't do that cuz it said
Token * was not valid. Valid tokens: , FROM INTO.

you can't put subqueries as an item in the select statement anyway, just in the where or having or exists or in type clauses at the end keep in mind the AS400 is ancient!

I seriously doubt there is a way to do this at all. Here's the table data:
ORDER_NUM PART_NUM NUM_ORDERED QUOTED_PRICE
21608______AT94________11__________21.95
21610______DR93________1__________495.00
21610______DW11________1__________399.99
21613______KL62________4__________329.95
21614______KT03________2__________595.00
21617______BV06________2__________794.95
21617______CD52________4__________150.00
21619______DR93________1 __________495.00
21623______KV29________2__________1,290.00
the same order_num's need to be crunched together so that's a group by and you just can't run a sum on the quoted price field once the order nums are grouped together so there's absolutely no way to "display the order number and order total with order total being the sum of the number of units times the quoted price on each order line for each order" like the problem asks, is there?

[brucevde]

select order_num, SUM(num_ordered * quoted_price) as order_total
from order_line 
group by order_num
order by order_num

[Desolator144]

*groan* omg that worked I'm gonna add an extra S to the AS400 label when I get back to school