This project has moved and is read-only. For the latest updates, please go here.
0

Closed

Group by in CTE causes ArgumentException

description

WITH D AS (
 SELECT e.EmployeeID,
   e.FirstName + ' ' + e.LastName AS FullName,
   TRIM(t.TerritoryDescription) AS Territory,
   TRIM(r.RegionDescription) AS Region
 FROM Employees e
    INNER JOIN EmployeeTerritories et ON et.EmployeeID = e.EmployeeID
    INNER JOIN Territories t ON t.TerritoryID = et.TerritoryID
    INNER JOIN Region r ON r.RegionID = t.RegionID
), o AS (
 SELECT o.OrderID,
   o.OrderDate,
   o.EmployeeID,
   SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS BruttoPrice,
   SUM(od.Quantity * od.UnitPrice) AS NettoPrice
 FROM Orders o
    INNER JOIN [Order Details] od ON od.OrderID = o.OrderID
 GROUP BY o.OrderID, o.OrderDate, o.EmployeeID
)
SELECT D.FullName,
  D.Territory,
  D.Region,
  o.*
FROM D INNER JOIN o ON o.EmployeeID = D.EmployeeID
System.ArgumentException: IComparer (or the IComparable methods it relies upon) did not return zero when Array.Sort called x. CompareTo(x). x: 'System.Object[]'  x's type: 'Object[]' The IComparer: 'NQuery.Runtime.ExecutionPlan.SortIterator+RowComparer'.
   at System.Collections.Generic.ArraySortHelper1.SwapIfGreaterWithItems[TValue](T[] keys, TValue[] values, IComparer1 comparer, Int32 a, Int32 b)
   at System.Collections.Generic.ArraySortHelper1.QuickSort[TValue](T[] keys, TValue[] values, Int32 left, Int32 right, IComparer1 comparer)
   at System.Collections.Generic.ArraySortHelper1.Sort[TValue](T[] keys, TValue[] values, Int32 index, Int32 length, IComparer1 comparer)
   at System.Collections.Generic.ArraySortHelper1.Sort(T[] items, Int32 index, Int32 length, IComparer1 comparer)
   at System.Array.Sort[T](T[] array, Int32 index, Int32 length, IComparer1 comparer)
   at System.Collections.Generic.List
1.Sort(Int32 index, Int32 count, IComparer1 comparer)
   at System.Collections.Generic.List
1.Sort(IComparer`1 comparer)
   at NQuery.Runtime.ExecutionPlan.SortIterator.SortInput() in N:\Trunk\Src\NQuery\Execution Plan\SortIterator.cs:line 100
   at NQuery.Runtime.ExecutionPlan.SortIterator.Read() in N:\Trunk\Src\NQuery\Execution Plan\SortIterator.cs:line 113
   at NQuery.Runtime.ExecutionPlan.StatisticsIterator.Read() in N:\Trunk\Src\NQuery\Execution Plan\StatisticsIterator.cs:line 25
   at NQuery.Runtime.ExecutionPlan.StreamAggregateIterator.Open() in N:\Trunk\Src\NQuery\Execution Plan\StreamAggregateIterator.cs:line 128
   at NQuery.Runtime.ExecutionPlan.StatisticsIterator.Open() in N:\Trunk\Src\NQuery\Execution Plan\StatisticsIterator.cs:line 20
   at NQuery.Runtime.ExecutionPlan.HashMatchIterator.Open() in N:\Trunk\Src\NQuery\Execution Plan\HashMatchIterator.cs:line 41
   at NQuery.Runtime.ExecutionPlan.StatisticsIterator.Open() in N:\Trunk\Src\NQuery\Execution Plan\StatisticsIterator.cs:line 20
   at NQuery.Runtime.ExecutionPlan.ResultIterator.Open() in N:\Trunk\Src\NQuery\Execution Plan\ResultIterator.cs:line 13
   at NQuery.Query.ExecuteDataTable() in N:\Trunk\Src\NQuery\API\Query.cs:line 132
   at NQuery.Gui.QueryDocument.Execute() in N:\Trunk\Src\NQuery.Gui\QueryDocument.cs:line 196
   at NQuery.Gui.QueryDocument.NQuery.Gui.IQueryHandler.Execute() in N:\Trunk\Src\NQuery.Gui\QueryDocument.cs:line 135
   at NQuery.Gui.MainForm.executeToolStripMenuItem_Click(Object sender, EventArgs e) in N:\Trunk\Src\NQuery.Gui\MainForm.cs:line 282
   at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
   at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
   at System.Windows.Forms.ToolStripMenuItem.ProcessCmdKey(Message& m, Keys keyData)
   at System.Windows.Forms.ToolStripManager.ProcessShortcut(Message& m, Keys shortcut)
   at System.Windows.Forms.Form.ProcessCmdKey(Message& msg, Keys keyData)
   at System.Windows.Forms.ContainerControl.ProcessCmdKey(Message& msg, Keys keyData)
   at TD.SandDock.DocumentContainer.ProcessCmdKey(Message& msg, Keys keyData)
   at System.Windows.Forms.ContainerControl.ProcessCmdKey(Message& msg, Keys keyData)
   at TD.SandDock.DockControl.ProcessCmdKey(Message& msg, Keys keyData)
   at System.Windows.Forms.ContainerControl.ProcessCmdKey(Message& msg, Keys keyData)
   at System.Windows.Forms.Control.PreProcessMessage(Message& msg)
   at System.Windows.Forms.Control.PreProcessControlMessageInternal(Control target, Message& msg)
   at System.Windows.Forms.Application.ThreadContext.PreTranslateMessage(MSG& msg)
Closed Oct 31, 2014 at 7:13 AM by terrajobst

comments