Re: Format an Update with calculation

On Tue, Dec 18, 2018 at 5:51 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 12/17/18 11:14 PM, Bret Stern wrote:
> > My statement below updates the pricing no problem, but I want it to be
> > formatted with 2 dec points eg (43.23).
> >
> > Started playing with to_numeric but can't figure it out. Lots of
> > examples with to_char in the
> > manual, but still searching for answer.
> >
> > Can it be done?
> >
> > I want suggested_retail_price to be formatted to 2 decimal points
> >
> > UPDATE im_ci_item_transfer
> > SET suggested_retail_price=(suggested_retail_price +
> > (suggested_retail_price * .13))
> > WHERE item_code='0025881P2';
> >
> > Feeling lazy, sorry guys
>
> In addition to what Pavel posted:
>
> select round(43.2335, 2);
>
> round
> -------
> 43.23
>
>
>
I think this discussion is missing an important point, which is the
difference between how a value is stored (i.e., what ends up as
suggested_retail_price in your table), and how it is formatted when you
select or use it. Whatever value you select in this update is going to get
converted to the column's datatype anyway. You haven't told us what the
datatype for s_r_p is, but consider this example:

CREATE TEMP TABLE price (
my_dec_2 DECIMAL(8,2),
my_numeric NUMERIC,
my_numeric_2 NUMERIC (8,2),
my_money MONEY
);
WITH num as (SELECT 4.2375914 AS base)
INSERT INTO price
SELECT base,base,base,base FROM num;

SELECT * FROM price;

my_dec_2 | my_numeric | my_numeric_2 | my_money
----------+------------+--------------+----------
4.24 | 4.2375914 | 4.24 | $4.24

So if you want your prices to be limited to 2 decimal places, just define
the columns as such. All the formatting mentioned in this thread is only
needed for outputting, selecting on the fly, etc.

Cheers,
Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.