asked on

Substituting a 'NULL' as none

Hello, I am trying to subsititute NULL results and replace them as none.

coalesce(cast(avg(purchase_price)as varchar(4)), 'none')

This statement gives me an error.

Arithmetic overflow error converting numeric to data type varchar.

Any idea on what I am doing incorrectly?

Thanks.

Anthony Perkins

Please post your entire query so that we can suggest a workaround.

Jintonix415

ASKER

Select  M.menu_id, 
		menu_desc,
		Count(*) As NumberofMenus,
		AVG(purchase_price) As AvgPurchasePrice,
		coalesce(cast(avg(purchase_price)as varchar(4)), 'none')  as AvgCostPrice
From	Menu M join menu_content MC on M.Menu_id = MC.menu_id
		join items I on MC.item_id = I.item_id
Group by M.menu_id, menu_desc

Open in new window

If I remove the

coalesce(cast(avg(purchase_price)as varchar(4)), 'none') as AvgCostPrice

It is fine.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jintonix415

ASKER

Ahhh thank you. Do you also happen to know how to round out to two decimal places for that particular line? If you prefer I can create a new question for this, thank you!

Anthony Perkins

See if this works for you:

SELECT  M.menu_id,
        menu_desc,
        COUNT(*) AS NumberofMenus,
        AVG(purchase_price) AS AvgPurchasePrice,
        COALESCE(CAST(ROUND(AVG(purchase_price), 2) AS varchar(20)), 'none') AS AvgCostPrice
FROM    Menu M
        JOIN menu_content MC ON M.Menu_id = MC.menu_id
        JOIN items I ON MC.item_id = I.item_id
GROUP BY M.menu_id,
        menu_desc

Open in new window

Jintonix415

ASKER

Thanks, but the results show as 45.260000. I would like to get rid of the 4 zeros to the right.

Anthony Perkins

>>I would like to get rid of the 4 zeros to the right. <<
Than you need to be more precise: You not only want to round you also want to truncate trailing zeros as in something like this:

SELECT  M.menu_id,
        menu_desc,
        COUNT(*) AS NumberofMenus,
        AVG(purchase_price) AS AvgPurchasePrice,
        COALESCE(CAST(CAST(AVG(purchase_price) AS decimal(20,2)) AS varchar(20)), 'none') AS AvgCostPrice
FROM    Menu M
        JOIN menu_content MC ON M.Menu_id = MC.menu_id
        JOIN items I ON MC.item_id = I.item_id
GROUP BY M.menu_id,
        menu_desc

Open in new window

Jintonix415

ASKER

Wow, genius indeed. Thank you!!!