7 Replies Latest reply on Mar 16, 2019 4:02 AM by Gaz in Oz

    dg4msql

    user10226782

      I am having a very strange problem with dg4msql.

      I am running select statement from oracle db to a sql table with dblink

      eg "select * from test_table@DG4MSQL".

      The sql table has rows with decimal numbers eg 1.012

      The result from select statement is 1

      Why .012 is truncated?

      I have define HS_LANGUAGE in init file

      Thanks for your time.

        • 1. Re: dg4msql
          L. Fernigrini

          Can you share the HS configuration? you mention you configured HS_LANGUAGE but did not shown how it is configured.

           

          You should have the configuration set similar to what SQL Server is configured.

           

          Problems with SELECTing numeric values from MS SQL Server

          • 2. Re: dg4msql
            user10226782

            HS_FDS_CONNECT_INFO= host_name//database_name

            HS_FDS_PROC_IS_FUNC=false

            HS_FDS_RESULTSET_SUPPORT=true

            HS_LANGUAGE=GREECE_GREEK.EL8ISO8859P7

             

            Sql server is configured with collation Greek_CI_AS.

            • 3. Re: dg4msql
              EdStevens

              user10226782 wrote:

               

              I am having a very strange problem with dg4msql.

              I am running select statement from oracle db to a sql table with dblink

              eg "select * from test_table@DG4MSQL".

              The sql table has rows with decimal numbers eg 1.012

              The result from select statement is 1

              Why .012 is truncated?

              It's a matter of number format display and has nothing to do with the fact that the data comes from MSSQL.

              Here's pure oracle demo that shows exactly:

               

              SQL> conn scott/tiger

              Connected.

              SQL> create table mydemo (mynum number(6,3));

               

              Table created.

               

              SQL> insert into mydemo values (0.012);

               

              1 row created.

               

              SQL> commit;

               

              Commit complete.

               

              SQL> select * from mydemo;

               

                  MYNUM

              ----------

                    .012

               

              1 row selected.

               

              SQL> col mynum for 000.999

              SQL> select * from mydemo;

               

                MYNUM

              --------

              000.012

               

              1 row selected.

              Leading zeros to the left of a decimal are considered (mathematically) as 'non-significant digits'.  When it comes to computers, most client programs (and it is the client program that is responsible for formatting and displaying the data) will not display non-siginificant digits.  In the demo above, before my second SELECT I use the COL command to tell sqlplus to format the column MYNUM with leading non-significant zeros.

               

              You can read more about the use of the COLUMN FORMAT command in sqlplus, here: https://docs.oracle.com/database/121/SQPUG/ch_twelve013.htm#SQPUG034

              If your client program is something other than sqlplus, you will have to deal with the issue using the facilities of that program.  It's not a data issue, it's a rendering issue by the client program.

               

              --- edit -

              Sorry, I misread the original.  I didn't notice that you said it was showing a '1' to the left of the decimal but truncating everything to the right.  That said, I still strongly suspect it is a formatting issue in your client program rather than a data issue.  Can you put together a full demo, as I did, instead of just trying to describe what you see?

              • 4. Re: dg4msql
                L. Fernigrini

                It may be a format issue, as you mentioned.

                 

                But the problem is not leading zeros but actual "truncating" or "rounding" of decimals.

                 

                OP could verify if the problem is just a display format by verifying the format in the IDE that is being used to query the SQL database.

                • 5. Re: dg4msql
                  user10226782

                  I am running the select statement from toad.

                  The truth is that i have not test it from other client program.

                  I wiil give it a try and let you now.

                  Thanks for quick response..    

                  • 6. Re: dg4msql
                    EdStevens

                    L. Fernigrini wrote:

                     

                    It may be a format issue, as you mentioned.

                     

                    But the problem is not leading zeros but actual "truncating" or "rounding" of decimals.

                     

                    OP could verify if the problem is just a display format by verifying the format in the IDE that is being used to query the SQL database.

                    Yes, I caught that and added a correction to  my post even as you were posting. 

                    • 7. Re: dg4msql
                      Gaz in Oz

                      In toad, the "solution" depends on what you want achieve and how you are executing the query, F9 run query or F5 run script. Apparently tOad uses 2 different "engines to process/run depending on which button is pressed.

                      One way to ensure output with integers and decimals is to use TO_CHAR() in the query. (and take a look at Number Format models)

                      For example:

                      select TO_CHAR(1.012, '99999999.9999') num from dual;

                       

                      NUM

                      -------------

                              1.012

                       

                      When using F5, you can set the default number format for all numeric data by:

                      set numformat 99999999.999

                      select 1.012 num from dual;

                       

                                NUM

                      -------------

                              1.012

                       

                      There may be an option to control the data grid format too (F9 output) under "Options -> Data Grids -> Data", but I don't have toAd at hand to verify.