Average and Inner Join in SQL

Question

I'm very new to SQL thus why i ask this question. I'm trying to work out the average delivery time for items that have DO and DO NOT have a warranty. The warranty can be presented as either 1 or NaN so the column is not a NUMERIC. I've written what I think the code should look like but come across errors.

SHIPPING_TABLE:

(Composite key)

Item_id
Basket_id
seller_id
warranty
postage_class 

FIRST_CLASS:

(all as a primary key)

Item_id
Basket_id
seller_id
warranty
postage_class
delivery_time

---

create table AVG_DELIVERY_TIME as 
(
    SELECT DELIVERY_TIME AVG 
    ,(DELIVERY_TIME) 
    FROM 
    (
        SELECT SHIPPING_TABLE_LINK.warranty
        ,FIRST_CLASS.DELIVERY_TIME
        FROM FIRST_CLASS
        INNER JOIN SHIPPING_TABLE_LINK 
            ON SHIPPING_TABLE_LINK.warranty = FIRST_CLASS.DELIVERY_TIME
    )
);

Error report - ORA-01722: invalid number 01722. 00000 - "invalid number" *Cause: The specified number was invalid. *Action: Specify a valid number.

Answer 1

I believe you can update the values in your warranty column to be 0 rather than NaN by using the following code

UPDATE table_name SET warranty = 0 WHERE warranty ="NaN;

you can then create your new table and specify this column as numeric rather than varchar. Hope that helps