[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

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.
0
Jintonix415
Asked:
Jintonix415
  • 4
  • 4
1 Solution
 
Anthony PerkinsCommented:
Please post your entire query so that we can suggest a workaround.
0
 
Jintonix415Author Commented:
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.
0
 
Anthony PerkinsCommented:
You need to make the varchar size larger as in:
coalesce(cast(avg(purchase_price)as varchar(20)), 'none')  as AvgCostPrice
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Jintonix415Author Commented:
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!
0
 
Anthony PerkinsCommented:
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

0
 
Jintonix415Author Commented:
Thanks, but the results show as 45.260000. I would like to get rid of the 4 zeros to the right.
0
 
Anthony PerkinsCommented:
>>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

0
 
Jintonix415Author Commented:
Wow, genius indeed. Thank you!!!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now