codefluent

Solution to Error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified

Reason for Error: I am creating a method on a TimeCard entity. The TimeCard entites relates to an Employee entity. The method sorts TimeCards by employee name.

Obvious Solution Copy the stored procedure from the [project_name]_procedures.sql file and add the sorted columns to the select statement. Then create a raw method using the stored procedure.

Question: Is there a better solution. I don't like the obvious solution because it will break if I add additional properties to the TimeCard entity.

Solution

  • The SQL generated by CFE is not valid as explain in this post: How SQL DISTINCT and ORDER BY are Related. I think there are 2 workarounds:

    1. Remove the DISTINCT from the stored procedure if it is not needed:

      <cf:method name="LoadForActiveEmployeesSortedByEmployee" distinct="false" ... />

    2. Use a raw method with the valid SQL query:

      <cf:method body="LOAD() raw" rawBody="SELECT DISTINCT $TimeCard{Columns}$, EmployeeName FROM $TimeCard$ JOIN ... WHERE ... ORDER BY ...">