sqloracle-databasenumbersleading-zeroadempiere

Create leading zero in Oracle

I am using Adempiere which has database Oracle

I have window called Stock Code from table called M_StockCode

The fields are Code and Description.

Currently, Code data type is Number and Description is Varchar2

I want to input Sparepart with Code 01, and Body Repair with Code 02.

As I input the data in Adempiere and save it, what will show is Sparepart with Code 1 (without leading zero)

I've tried putting LPAD function but it's still failed.

How can I put 01 both in Adempiere interface and in database?

Any suggestion will be appreciated :)

Solution

  • A NUMBER cannot have leading zero, a STRING can.

    1. If you want to store the codes with leading zero in the database table, then you must use VARCHAR2 and not NUMBER.

    2. If you want to just display the number with leading zero, then use TO_CHAR to convert the number into string.

    For example,

    SQL> SELECT TO_CHAR(1, '00') FROM DUAL;

TO_
---
 01

    You could also use LPAD, but remember, the data type of the result would be a string and not a number.

    For example,

    SQL> SELECT LPAD(1, 2, '0') FROM DUAL;

LP
--
01