Calculation in oracle using SQL

Question

Hi I am using orcale and I have created a join between tables. I am now trying to insert a calculation but it keeps coming up with the same error message "ORA-00933: SQL command not properly ended" The code i have produced is this..

select   "CUSTOMER"."CUST_ID" as "CUST_ID",
 "CUSTOMER"."CUST_TITLE" as "CUST_TITLE",
 "CUSTOMER"."CUST_FORENAME" as "CUST_FORENAME",
 "CUSTOMER"."CUST_SURNAME" as "CUST_SURNAME",
 "CUSTOMER"."CUST_ADDRESS_1" as "CUST_ADDRESS_1",
 "CUSTOMER"."CUST_ADDRESS_2" as "CUST_ADDRESS_2",
 "CUSTOMER"."CUST_TOWN-CITY" as "CUST_TOWN-CITY",
 "CUSTOMER"."CUST_COUNTY" as "CUST_COUNTY",
 "CUSTOMER"."CUST_POST_CODE" as "CUST_POST_CODE",
 "CUSTOMER"."CUST_TEL" as "CUST_TEL",
 "CUSTOMER"."CUST_EMAIL" as "CUST_EMAIL",
 "MENU_ITEM"."PRODUCT_PRICE" as "PRODUCT_PRICE",
 "ORDER_ITEM"."QUANTITY" as "QUANTITY",
 "ORDER_ITEM"."ORDER_ID" as "ORDER_ID",
 "ORDER_DETAILS"."ORDER_ID" as "ORDER_ID"
from     "ORDER_DETAILS" "ORDER_DETAILS",
 "ORDER_ITEM" "ORDER_ITEM",
 "MENU_ITEM" "MENU_ITEM",
 "CUSTOMER" "CUSTOMER"
where   "ORDER_ITEM"."ORDER_ID"="ORDER_DETAILS"."ORDER_ID"
 and     "ORDER_DETAILS"."CUST_ID"="CUSTOMER"."CUST_ID"
 and     "MENU_ITEM"."MENU_ITEM_ID"="ORDER_ITEM"."MENU_ITEM_ID"`

this is the code that produces the error message

 select  "CUSTOMER"."CUST_ID" as "CUST_ID",
 "CUSTOMER"."CUST_TITLE" as "CUST_TITLE",
 "CUSTOMER"."CUST_FORENAME" as "CUST_FORENAME",
 "CUSTOMER"."CUST_SURNAME" as "CUST_SURNAME",
 "CUSTOMER"."CUST_ADDRESS_1" as "CUST_ADDRESS_1",
 "CUSTOMER"."CUST_ADDRESS_2" as "CUST_ADDRESS_2",
 "CUSTOMER"."CUST_TOWN-CITY" as "CUST_TOWN-CITY",
 "CUSTOMER"."CUST_COUNTY" as "CUST_COUNTY",
 "CUSTOMER"."CUST_POST_CODE" as "CUST_POST_CODE",
 "CUSTOMER"."CUST_TEL" as "CUST_TEL",
 "CUSTOMER"."CUST_EMAIL" as "CUST_EMAIL",
 "MENU_ITEM"."PRODUCT_PRICE" as "PRODUCT_PRICE",
 "ORDER_ITEM"."QUANTITY" as "QUANTITY",
 "ORDER_ITEM"."ORDER_ID" as "ORDER_ID",
 "ORDER_DETAILS"."ORDER_ID" as "ORDER_ID"
   from  "ORDER_DETAILS" "ORDER_DETAILS",
 "ORDER_ITEM" "ORDER_ITEM",
 "MENU_ITEM" "MENU_ITEM",
 "CUSTOMER" "CUSTOMER"
where   "ORDER_ITEM"."ORDER_ID"="ORDER_DETAILS"."ORDER_ID"
 and     "ORDER_DETAILS"."CUST_ID"="CUSTOMER"."CUST_ID"
 and     "MENU_ITEM"."MENU_ITEM_ID"="ORDER_ITEM"."MENU_ITEM_ID"
SELECT PRODUCT_PRICE,
       QUANTITY, 
       PRODUCT_PRICE * QUANTITY AS "TOTAL SPENT"
FROM   MENU_ITEM,ORDER_ITEM

Please post the actual query that's producing the error-message. — ruakh
– ruakh, Commented Mar 18, 2013 at 21:24
@user2179333 Your post contains 2 queries. Which one produces the error: 1st, 2nd, or both? — PM 77-1
– PM 77-1, Commented Mar 18, 2013 at 21:33
A few things come to mind. Be sure both columns are indeed numerical. Also, you have no joining conditions on your lower code ("MENU_ITEM"."MENU_ITEM_ID"="ORDER_ITEM"."MENU_ITEM_ID"). — David Starkey
– David Starkey, Commented Mar 18, 2013 at 21:33

JoshL · Accepted Answer · 2013-03-18 21:57:46Z

The problem with the SQL is that your second "SELECT" statement opens a new SQL command. If you want to add a new column called "TOTAL_SPENT" to your query that multiplies PRODUCT_PRICE and QUANTITY, add it into the SELECT list of the main query. Adding a second SELECT statement will not merge the results into the first one.

You want something like this:

select  "CUSTOMER"."CUST_ID" as "CUST_ID",
 "CUSTOMER"."CUST_TITLE" as "CUST_TITLE",
 "CUSTOMER"."CUST_FORENAME" as "CUST_FORENAME",
 "CUSTOMER"."CUST_SURNAME" as "CUST_SURNAME",
 "CUSTOMER"."CUST_ADDRESS_1" as "CUST_ADDRESS_1",
 "CUSTOMER"."CUST_ADDRESS_2" as "CUST_ADDRESS_2",
 "CUSTOMER"."CUST_TOWN-CITY" as "CUST_TOWN-CITY",
 "CUSTOMER"."CUST_COUNTY" as "CUST_COUNTY",
 "CUSTOMER"."CUST_POST_CODE" as "CUST_POST_CODE",
 "CUSTOMER"."CUST_TEL" as "CUST_TEL",
 "CUSTOMER"."CUST_EMAIL" as "CUST_EMAIL",
 "MENU_ITEM"."PRODUCT_PRICE" as "PRODUCT_PRICE",
 "ORDER_ITEM"."QUANTITY" as "QUANTITY",
 "ORDER_ITEM"."ORDER_ID" as "ORDER_ID",
 "ORDER_DETAILS"."ORDER_ID" as "ORDER_ID",
 PRODUCT_PRICE * QUANTITY AS "TOTAL SPENT"
   from  "ORDER_DETAILS" "ORDER_DETAILS",
 "ORDER_ITEM" "ORDER_ITEM",
 "MENU_ITEM" "MENU_ITEM",
 "CUSTOMER" "CUSTOMER"
where   "ORDER_ITEM"."ORDER_ID"="ORDER_DETAILS"."ORDER_ID"
 and     "ORDER_DETAILS"."CUST_ID"="CUSTOMER"."CUST_ID"
 and     "MENU_ITEM"."MENU_ITEM_ID"="ORDER_ITEM"."MENU_ITEM_ID"

Depending on what columns are in the other tables, you may need to qualify PRODUCT_PRICE and QUANTITY with their table names, like the other column references in the SQL.

thank you so much! it did work and you've saved me branding today is a wasted day on my assignment! thank you

Collectives™ on Stack Overflow

Calculation in oracle using SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related