Unexpected database connector error - A column has been specified more than once in the order by list.

[Former Member]

If a report is grouped on a date field and subdivided annually, then another group is created on the same date field and subdivided monthly, an "Unexpected database connector error" is thrown.  The underlying error for Microsoft SQL Server if the sql is run manually is "A column has been specified more than once in the order by list."

Platform:

Crystal Reports for Eclipse 2.0.13

To Reproduce:

1. Create a report.

2. Add a group on a date column and subdivide annually.

3. Add another group within that group, on the same date column, and subdivide monthly.

4. Show the SQL and try to run against Microsoft SQL Server.

The SQL created should look something like:

select "tranDate"

from "Table"

order by "tranDate", "tranDate"

The problem does NOT occur in Crystal Reports 2011 as the generated sql does NOT have the order by.

Is there a runtime setting in Crystal Reports for Eclipse to suppress the generation of the order by?  We know of a few workarounds by changing the report, however, we don't want to change 100's of reports.  We are looking for a fix or a runtime workaround.

[Former Member]

bump

[Former Member]

The grouping / sorting done in the report is (should be) different than any order specified by the query.  In theory, no matter how the SQL is formed, if you get the correct results back, the report should be able to group/sort/order in whatever organization you've set the report up to do.

I'm not sure what you're trying to do in the report, but from a MS SQL point of view, that error is correct.  That SQL shows you trying to order by the same column, more than once.

If you remove the second occurrence, how does your report run?

If you NEED to do it in the query, you have some options.  The main option being to use a UDF or function to get the date-part or date-level that you need to differentiate between the orders.

For instance, you could use the SQL function to extract just the YEAR from the date, and use that expression as your highest order, and then extract the month from the date and use that expression for a lower order.

As an example:

SELECT

   SomeColumn_1, SomeColumn_2, OtherColumn, tranDate

FROM

   theTable

ORDER BY

   YEAR(tranDate), MONTH(tranDate)

Even though TranDate is shown in the ORDER clause twice, it is in two different expressions -- and shouldn't throw an error.

But, back to the initial goal:  If you need an alternate way of grouping the report, and a top-level and secondary-level grouping on tranDate don't work, try creating report formulas that do the same thing by extracting the dateparts.  Give the formulas different names, and group on those formulas.  It might be a little more tedious, but it will probably work out with the same results.

[Former Member]

Sorry Marc, I wasn't clear.

The SQL is generated by Crystal Reports using Show SQL, it is not hand written.  I should have said "The SQL created by Crystal Reports will be:"

If you know of a way to modify the SQL created by Crystal Reports for Eclipse, that would be ideal!  Please let me know how to do it.

Finally, we know of plenty of ways to get around the problem by modifying the report.  However, we would like Crystal Reports for Eclipse to be fixed as we have many legacy reports that have this kind of grouping.  Otherwise, we are looking for something we can do at runtime.