using ansi sql syntax for formatting Numeric

Question

Hi

I am using two different databases for my project, Oracle and Apache Derby, and am trying as much as possible to use the ANSI SQL syntax supported by both of the databases.

I have a table with a column amount_paid NUMERIC(26,2).

My old code, which was using Oracle db, needed to retrieve value in this format

SELECT LTRIM(TO_CHAR(amount_paid,'9,999,999,999,999.99'))

How can I convert a numeric value to such a string in the format '9,999,999,999,999.99' using ANSI sql syntax?

Answer 1

I think this is the wrong approach. The format mask is for display purposes, so it really ought to be the concern of the presentation layer. All your data access layer should do is merely execute:

select amount_paid 
from your_table
where ....

This syntax will obviously work whatever database your app attaches to.

Then put the formatting code in the front-end, where it belongs.

Answer 2

My knowledge is not encylopedic but as far as I know there isn't an ANSI function to do what you want (although I'd be glad to find out I'm wrong :-). CONVERT converts between character sets but does not, as best I can see, do the formatting work you want. CAST converts values between data types but, again, doesn't do formatting.

If Derby doesn't support the Oracle-style TO_CHAR function you may have to roll your own function, let's call it MY_TO_CHAR. In Oracle the implementation might be

FUNCTION MY_TO_CHAR(nValue IN NUMBER,
                    strOracle_format IN VARCHAR2,
                    strDerby_format  IN VARCHAR2)
  RETURN VARCHAR2
IS BEGIN
  RETURN TO_CHAR(nValue, strOracle_format);
END MY_TO_CHAR;

In Derby you'd want to define this function in a similar manner, taking the appropriate value and format and invoking Derby's equivalent of TO_CHAR with the Derby formatting string.

EDIT: I agree with @APC - a lot of these issues disappear if you don't require the backend to do what is basically front-end work.

Share and enjoy.