?
Solved

Math operations in SQL query

Posted on 2009-12-30
6
Medium Priority
?
382 Views
Last Modified: 2013-12-07
I have this query:

SELECT orders.issuedate, supplier.supname, orderline.ordref, product.prodcode, product.producttitle, orderline.quantity,
productprice.costprice, productprice.vat, productprice.costprice+productprice.vat as itemcost, quantity*itemcost as itemtotal
FROM orderline, product, productprice, orders, supplier
WHERE orders.ordref = orderline.ordref
AND orders.supid = supplier.supid
AND product.prodcode = productprice.prodcode
AND orderline.prodcode = product.prodcode
AND supname = 'Toyshop'
AND productprice.enddate is null
ORDER BY ORDREF

.. and get this error:

Error at Command Line:2 Column:104
Error report:
SQL Error: ORA-00904: "ITEMCOST": invalid identifier
00904. 00000 -  "%s: invalid identifier"

I am trying to create a derived attribute - item total that shows price including vat and then multiply this 'item total' by the quantity to get the total price for an item on an order.

How can I do it. Can someone help me find the solution to this? Thanks a lot
0
Comment
Question by:czechmate1976
4 Comments
 
LVL 13

Accepted Solution

by:
zadeveloper earned 336 total points
ID: 26144560
you cannot use the dirived col name in the select statment, try:
SELECT orders.issuedate, supplier.supname, orderline.ordref, product.prodcode, product.producttitle, orderline.quantity, 
productprice.costprice, productprice.vat, productprice.costprice+productprice.vat as itemcost, quantity*(productprice.costprice+productprice.vat) as itemtotal 
FROM orderline, product, productprice, orders, supplier
WHERE orders.ordref = orderline.ordref
AND orders.supid = supplier.supid
AND product.prodcode = productprice.prodcode
AND orderline.prodcode = product.prodcode
AND supname = 'Toyshop'
AND productprice.enddate is null
ORDER BY ORDREF

Open in new window

0
 
LVL 15

Assisted Solution

by:ludofulop
ludofulop earned 332 total points
ID: 26144561
SELECT orders.issuedate, supplier.supname, orderline.ordref, product.prodcode, product.producttitle, orderline.quantity,
productprice.costprice, productprice.vat, productprice.costprice+productprice.vat as itemcost, quantity*(productprice.costprice+productprice.vat) as itemtotal
FROM orderline, product, productprice, orders, supplier
WHERE orders.ordref = orderline.ordref
AND orders.supid = supplier.supid
AND product.prodcode = productprice.prodcode
AND orderline.prodcode = product.prodcode
AND supname = 'Toyshop'
AND productprice.enddate is null
ORDER BY ORDREF
0
 
LVL 14

Assisted Solution

by:shru_0409
shru_0409 earned 332 total points
ID: 26144588
u can try like this..

SELECT z.*, z.quantity * z.itemcost AS itemtotal
  FROM (SELECT   orders.issuedate, supplier.supname, orderline.ordref,
                 product.prodcode, product.producttitle, orderline.quantity,
                 productprice.costprice, productprice.vat,
                 productprice.costprice + productprice.vat AS itemcost
            FROM orderline, product, productprice, orders, supplier
           WHERE orders.ordref = orderline.ordref
             AND orders.supid = supplier.supid
             AND product.prodcode = productprice.prodcode
             AND orderline.prodcode = product.prodcode
             AND supname = 'Toyshop'
             AND productprice.enddate IS NULL
        ORDER BY ordref) z
0
 

Author Closing Comment

by:czechmate1976
ID: 31671140
Absolutely spot on - I figured it out just before getting the confirmation from you guys. Anyway, thanks a lot.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question