Re: Returning formatted output

Tauren Mills wrote: > > Is there a way to return an expression as a Money type with set decimal > point precision? For instance, in the following select, > items.sale_price is MONEY(6,2) and sales.discount is INTEGER. The > equation sometimes returns results that have more than 2 decimal places. > > SELECT items.sale_price - ((items.sale_price * sales.discount) / 100), > items.name, sales.name, sales.discount, items.sale_price > FROM sales, items WHERE sales.sale_id = items.sale_id; > > I am using Informix Online Workgroup Server 7.12 on Sun Solaris 2.5.1 Try this: SELECT ROUND(items.sale_price - ((items.sale_price * sales.discount) / 100),2), items.name, sales.name, sales.discount, items.sale_price FROM sales, items WHERE sales.sale_id = items.sale_id; The second parameter is the number of decimal places, default is 0. If you don't want to round UP, then TRUNC will round DOWN, as in: SELECT TRUNC(items.sale_price - ((items.sale_price * sales.discount) / 100),2), items.name, sales.name, sales.discount, items.sale_price FROM sales, items WHERE sales.sale_id = items.sale_id; Hope that helps, -- Mark. +------------------------------------------------------------------+ |Mark D. Stock - The West Solutions Group http://www.west.co.za | | The Informix FAQ is at http://www.iiug.org | |mailto:marks@west.co.za +-----------------------------------------+ |Tel: +27 11 803 2151 |If it doesn't work, force it! | |Fax: +27 11 803 2189 |If it breaks, it needed replacing anyway!| |Cell: +27 83 250 2325 |Well, that's how I code anyway! | +------------------------+-----------------------------------------+