SQL-Statement Round odd numbers

SQL-Statement Round odd numbers

Postby Pepone » Mon 16 Sep 2013 07:11

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
Pepone
 
Posts: 1
Joined: Mon 16 Sep 2013 06:47

Re: SQL-Statement Round odd numbers

Postby Pinturiccio » Tue 17 Sep 2013 15:43

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);
Pinturiccio
Devart Team
 
Posts: 1928
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle