7 Replies Latest reply on Jul 10, 2018 2:44 PM by fac586

    Oracle apex not displaying leading zero

    Arunhere

      I have this strange thing happening.

       

      I have a table where i have data with leading zero's for a column(number type). When i query in apex - SQL Workshop

      Select colA from tab1; the output is giving as .87,

      but when i'm querying the same in SQL developer i'm getting 0.87

       

      I'm not sure where the leading zero's are going when checking in Apex. Are there any setting i have to change?We are using Apex 3.2 version.

        • 1. Re: Oracle apex not displaying leading zero
          fac586

          3440464 wrote:

          Update your forum profile with a recognisable username instead of "3440464": Video tutorial how to change nickname available

           

          Help us to help you. Always follow these guidelines when posting a question: how to get answers from forum

          I have this strange thing happening.

           

          I have a table where i have data with leading zero's for a column(number type). When i query in apex - SQL Workshop

          Select colA from tab1; the output is giving as .87,

          but when i'm querying the same in SQL developer i'm getting 0.87

           

          I'm not sure where the leading zero's are going when checking in Apex. Are there any setting i have to change?

          There is nothing strange happening. SQL Workshop and SQL Developer just have different ways of displaying the same information. Numbers are not stored as strings in the Oracle database, so there are no leading zeroes to start with. Display formats are applied by the software used to present the data.

           

          There are no preferences or settings to control number formats in SQL Workshop. If specific formatting is necessary, do this explicitly in the query using to_char and a suitable format model.

          We are using Apex 3.2 version.

          Support for APEX 3.2 ended in February 2012. It's time to seriously think about upgrading.

          • 2. Re: Oracle apex not displaying leading zero
            Arunhere

            Thanks for the comments fac586. I dont want to do any format. Users want to display the values as it is coming from the table in report. If i format explicitly using to_char then it is affecting the normal value. For example to_char(colA,'0.99') will give me 0.87 when value is .87 but we do have values without decimal also. in that case it will give me ###. Basically i want to display value as it is(but add 0 if value is starting with decimal). It should not add trailing zero also.Is there any way to achieve this?

            • 3. Re: Oracle apex not displaying leading zero
              Alli Pierre Yotti

              Did you try that?

              select to_char(colA,'999G999G999G999G990D00MI') from dual

              • 4. Re: Oracle apex not displaying leading zero
                fac586

                3440464 wrote:

                 

                Thanks for the comments fac586. I dont want to do any format. Users want to display the values as it is coming from the table in report. If i format explicitly using to_char then it is affecting the normal value. For example to_char(colA,'0.99') will give me 0.87 when value is .87 but we do have values without decimal also. in that case it will give me ###. Basically i want to display value as it is(but add 0 if value is starting with decimal). It should not add trailing zero also.Is there any way to achieve this?

                What is the data type of this column?

                • 5. Re: Oracle apex not displaying leading zero
                  Arunhere

                  Yes, it is adding trailing zero. It should not add decimal and extra 0

                   

                  select to_char(162,'999G999G999G999G990D00MI') from dual

                  ouput : 162.00

                  • 6. Re: Oracle apex not displaying leading zero
                    Arunhere

                    It is number datatype

                    • 7. Re: Oracle apex not displaying leading zero
                      fac586

                      Arunhere wrote:

                       

                      It is number datatype

                      Then it does not have "values without decimal also". All values are stored in the table using the same internal representation. The concept of the column values having leading zeroes simply does not exist. It is entirely up to the presentation layer how the output is formatted, and SQL Developer's defaults happen to be different to those in the APEX SQL Workshop.