Skip to Content
author's profile photo Former Member
Former Member

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

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.

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Sep 11, 2012 at 11:58 PM

    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.


    Add a comment
    10|10000 characters needed characters exceeded

    • 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.

  • author's profile photo Former Member
    Former Member
    Posted on Nov 16, 2012 at 05:46 AM

    bump

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.