SQL-Statement Round odd numbers

[Pepone]

Hi!

I have issues with the rounds within a SQL-Statement.
If I want to round to odd numbers, an additional 0 will be concatenated.
Here is a small test program to reproduce this issue:

Code: Select all

        delegate void SetTableCallback(DataTable Table, DataGrid Grid);

        public AboutDialog()
        {
            InitializeComponent();
            DataTable Table = new DataTable();
            Table.Columns.Add("Rounded Points");
            Table.Columns.Add("Rounded Value");

            OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder
            {
                Direct = true,
                Server = "shilapp56",
                ServiceName = "ORAPRD34",
                Port = 1521,
                UserId = "PSC_TEST",
                Password = "PSC_TEST"
            };

            OracleConnection Connection = new OracleConnection(oraCSB.ConnectionString);
            Connection.Open();

            OracleCommand Command = Connection.CreateCommand();
            for (int i = 0; i < 5; i++)
            {
                Command.CommandText = "SELECT " + i + ", ROUND(COLUMN3," + i + ") as \"Round to " + i + " Points\" FROM TEST WHERE COLUMN1 = '1'";

                using (IDataReader Reader = Command.ExecuteReader())
                {
                    while (Reader.Read())
                    {
                        DataRow Row = Table.NewRow();
                        Row[0] = Reader.GetValue(0);
                        Row[1] = Reader.GetValue(1);

                        Table.Rows.Add(Row);
                    }
                }
            }

            setTable(Table, DataGridTest);

        }

        /// <summary>
        /// Sets the table for report.
        /// </summary>
        /// <param name="newtable">The newtable.</param>
        private void setTable(DataTable newtable, DataGrid Grid)
        {
            if (!Grid.Dispatcher.CheckAccess())
            {
                SetTableCallback d = new SetTableCallback(setTable);
                this.Dispatcher.Invoke(d, new object[] { newtable });
            }
            else
            {
                Grid.Columns.Clear();

                foreach (DataColumn Col in newtable.Columns)
                {
                    Grid.Columns.Add(
                        new DataGridTextColumn
                        {
                            Header = Col.ColumnName,
                            Binding = new System.Windows.Data.Binding(String.Format("[{0}]", Col.ColumnName))
                        });
                }

                Grid.DataContext = newtable;
                Grid.Items.Refresh();
            }
        }

I am using the Devart.Data.Oracle Version 7.9.322.0

Oracle Server version is:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

If you need anything else please let me know.

Best regards
Jan

[Pinturiccio]

We have investigated your code and got the following results. In the following code:

Code: Select all

for (int i = 0; i < 5; i++)
{
    Command.CommandText = "SELECT " + i + ", ROUND(COLUMN3," + i + ") as \"Round to " + i + " Points\" FROM TEST 

WHERE COLUMN1 = '1'";
    using (IDataReader Reader = Command.ExecuteReader())
    {
        while (Reader.Read())
        {
            DataRow Row = Table.NewRow();
            Row[0] = Reader.GetValue(0);
            Row[1] = Reader.GetValue(1);
            Table.Rows.Add(Row);
        }
    }
}

Reader.GetValue(1) returns, for example 12,3 when i=1. Then this value is assigned to Row[1]. When converting Decimal to String an extra zero is added in case when fractional part is odd. This behavior is not related to our provider, it is caused by the type conversion. ODP.NET has the same behavior.

As a workaround, you may replace the following code:

Code: Select all

Row[1] = Reader.GetValue(1);

with

Code: Select all

Row[1] = Reader.GetDouble(1);