Help with data management in Oracle, how to use the TO_CHAR for a NUMBER data type?

Question

Help with data management in Oracle, how to use the TO_CHAR for a NUMBER data type?

Navigation

#1 by (1 votes)

1

I have a query which obtains data of type NUMBER in the BD, what I do is show the result of the query in a .csv file and at the time of showing that data the fields of type NUMBER which are (IMPORTE_UNO, IMPORTE_DOS) it does not show decimals, for that I made use of to_char

My query is this

SELECT F.ID, F.NOMBRE, TO_CHAR(F.FECHA_INI,'DD/MM/YYYY'), TO_CHAR(F.FECHA_REGISTRO,'DD/MM/YYYY'), TO_CHAR(F.FECHA.CARGA,'DD/MM/YYYY'), TO_CHAR(F.IMPORTE_UNO,'fm9990.00'), TO_CHAR(F.IMPORTE_DOS,'fm9990.00'), F.ENTIDAD_REC
FROM FACTURA F
WHERE (?1 IS NULL OR  F.ID =?1)
AND F.FECHA_INI &gt;= TO_DATE(?2 'DD/MM/YYYY')
AND F.FECHA_INI &lt;= TO_DATE(?3 'DD/MM/YYYY')

Those fields are defined like this in the BD

IMPORTE_UNO  NUMBER(12,2)
IMPORTE_DOS  NUMBER(16,2)

What I do not know and I would like to clarify, is that if the way I use the TO_CHAR(F.IMPORTE_DOS,'fm9990.00') is ok or should go 12 times the "9" on the left because that field is defined IMPORTE_UNO NUMBER(12,2) , I do not know much about Oracle and I do not know if it's correct, which means NUMBER (12,2) ??

In my java code those fields are mapped as Double

sql java oracle query

asked by Root93 26.07.2018 в 06:29

source

1 answer

Create automatic images and save it in Javascript or other What differences do the sendall & send functions have in python?

score 1 · Answer 1

The way you declare TO_CHAR(F.IMPORTE_DOS,'fm9990.00') is correct if it meets your goal. Let me explain, fm9990.00 means that you do not want spaces in leftover targets when adding "fm" and your biggest number is 9999.99 and the smallest is 0.00.

When you add 0.00 you want mandatory 1 whole and 2 decimals:

FORMAT: ENTRY > DEPARTURE

0.0: 0 > 0.0
0.0: 2 > 2.0
0.00: 0 > 2.00
000.00: 5.5 > 005.50

By adding the 9 optional number signs:

90.00: 10 > 10.00
90.00: 1 > 1.00
90.00: 200 > #####

- EDITED --- A real example of BBDD where INVERSION is of type NUMBER(5,2) means that "5" is the total number of digits where of the total (5) will have 2 decimals that is, your largest number will be the 999.99 and the minor -999.99 (precision, scale).

SELECT INVERSION FROM EXPE ->
   3000,59
         0
        26
      35,5

This is what Oracle normally returns. Now look what happens when you add the to_char ():

SELECT to_char(INVERSION, 'fm9990.00') FROM EXPE ->
3000.59
0.00
26.00
35.50

Notice how the result is formatted on the left and has changed the "," for a ".", this may be useful because in the end you will work with EXEL and not have to configure anything.

If we remove the "fm" it returns the following:

SELECT to_char(INVERSION, '9990.00') FROM EXPE ->
3000.59
   0.00
  26.00
  35.50

I hope you find it useful. NOTE: Keep in mind that in Spanish the correct way to format a decimal number is 3,000.59 and it is something else as exel treats them.