SQL convert varchar into money

Question

I am attempting to calculate the total cost of items and and having that value into a new column called cost.

In my creation script the values are in characters but the data type is money.

CREATE VIEW ordered_pizzas_view
AS 
    SELECT 
        orderpizza_id, order_id, ..., 
        CONCAT(range_price, ' ', crust_surcharge, ' ', sauce_surcharge) AS 'cost'
    FROM 
        order_pizza 
    INNER JOIN 
        ...
    WHERE 
        'cost' = (SELECT SUM(CAST(range_price AS MONEY)) FROM pizza_range) +
                 (SELECT SUM(CAST(crust_surcharge AS MONEY)) FROM crust) +
                 (SELECT SUM(CAST(Sauce_surcharge AS MONEY)) FROM sauce)

Did you check the values in the table, are there non-numeric entries? — SAS, May 31 '17 at 12:22
I just noticed the SQL syntax is wrong, you can not compare money with char.. — SAS, May 31 '17 at 12:31

score 4 · Accepted Answer · answered May 31 '17 at 12:30

What are you expecting from concat()? concat() returns a string value which based on your input is going to be three money values separated by a space, e.g. $1.00 $2.00 $0.50

Comparing that to a money value is going to throw an error. Perhaps you meant to just add the values?

(range_price + crust_surcharge + sauce_surcharge ) as [cost]

Note: Do not use string literals as aliases.

Also, your where clause is comparing a string literal ('cost') to a money data type from the combined subqueries.

where --'cost'
      (range_price + crust_surcharge + sauce_surcharge )
              = (select Sum(CAST(range_price as money)) from pizza_range) 
             + ( select Sum(CAST(crust_surcharge as money)) from crust ) 
             + ( select Sum(CAST(Sauce_surcharge as money)) from sauce )

Note: You can not use column aliases in the where clause, you must repeat the expression or reference the expression from a subquery/derived table or other operator.

Thanks SQLZim that answered one of my problems, much appreciated — MacGenius, May 31 '17 at 12:39

SQL convert varchar into money

1 Answers1