DSmith DSmith - 4 months ago 28
SQL Question

Assist with currency format

Good Day please can someone assist me with a currency format of ###,###,###.## everything I try I seem to be getting stuck - My code is as follows:

function CF_TOTAL_COSTFormula return Number is
total_cost number;
begin
select SUM(share_amount) into total_cost
from reservation_name rn,
reservation_daily_element_name rden
where rn.resv_name_id = rden.resv_name_id
and rn.resv_name_id = :resv_name_id
and trunc(rden.reservation_date) between trunc(rn.begin_date) and trunc(rn.end_date - 1);
return total_cost;
exception when others then
return 0;
end;

Answer

Your function returns a NUMBER. Numbers in Oracle have no format. If you want to return a formatted number, you need to return a VARCHAR2. Something like this:

function CF_TOTAL_COSTFormula return varchar2 is
total_cost varchar2(30);
begin
  select to_char(SUM(share_amount),'999,999,990.00') into total_cost
  from reservation_name rn,
         reservation_daily_element_name rden
  where rn.resv_name_id = rden.resv_name_id
  and rn.resv_name_id = :resv_name_id
  and trunc(rden.reservation_date) between trunc(rn.begin_date) and trunc(rn.end_date - 1);
  return total_cost;
exception when others then
    return 'error';
end;

As an aside, I gather from the name of your function that perhaps you are using Oracle Reports or Oracle BI Publisher. If so, you should continue to return a number and handle the display formatting in the layout, not the model.