SQL vs. Record Sort Order

[Montana751]

Crystal XI

When running Crystal - we (3rd party software application)pass infomration into the SQL query. Back in Crystal 8.5, this information would "Append" to the Where (if conditional) and Order by clauses of the Crystal SQL statement. I've noticed in Crystal XI, this is not the case, the Where and Order by are being OVERWRITTEN - thus ignoring the settings set in the RPT file.

Long story short, I am trying to figure out if the ORDER BY clause if it is NOT in the "Show SQL Query" statement in crystal, but shows up in the Record Sort Expert, is it being sorted? as the sort is a parameter passed at run time. In 8.5 it was in the SQL Order by, but not in XI, its only showing up under Record Sort Order.

Thus, whats actually getting sorted?

 

[lbass]

If it's in the record sort area, but not in the SQL query, it's getting sorted--just not on the server. If you go to report->performance information, it will tell you when the sorting occurs on the server. Otherwise it is being sorted at the client (locally).

-LB

 

[Montana751]

What is the Order of process, the Order By clause, then the Record Sort Order? Are both sort conditions being applied, just 1st at the server for the Order By, then all the records that return are sorted locally using the Record Sort Order?

 

[lbass]

If you look in the sort records area, the sort order will be shown there, whether or not it all passed to the server. If you go to database->show SQL query, you will see which of the sort fields were passed to the server.

Whatever you see passed to the server occurs first, and then the remaining sorts are done locally. I think if you add a sort field that doesn't pass, then any later sort fields will also be handled locally.

-LB

 

[Montana751]

Here is the cause of my confusion:

In crystal xi, I have a record sort order defined as:
Group #1: Account.Name - A
A - Class.Name
A - Department.Fax

the SQL statement Order By reads:
ORDER BY AccountPool.Name, Class.Name, Department.Fax

I always "assumed" that the reason we had to use the record sort order was just so the Order by would be in the syntax that Crystal wants, plus the fact in xi you can't manuall alter it anyway.

THEN, after running the report through a 3rd party front end which allows the user to select Additional sorts (that are suppose to be appended to the Crystal sort order), I added the Item.Amount A, Item.PostDate A, Item.RefNum D. I get the following results:

Record Sort Order now displays:

Group #1: Account.Name - A
A - Item.Amount
A - Item.PostDate
D - Item.RefNum

and the SQL Order by clause now reads:
ORDER BY AccountPool.Name, Item.Amount

I don't understand what the report is being sorted on. To me, its just going to sort by AccountPool.Name and Item.Amount as this is what the Order by clause has.

Is the final sort actually AccountPool.Name, Item.Amount (sorted at the server), then Item.PostDate and Item.RefNum are sorted locally?

Regardless that the sorts got all messed up after running it thru the 3rd party software, I'm trying to understand the actual sort order applied.

thanks for your help.

 

[lbass]

Yes, I believe this is the case:

Is the final sort actually AccountPool.Name, Item.Amount (sorted at the server), then Item.PostDate and Item.RefNum are sorted locally?

The front end is clearly overriding some earlier sorts, and I couldn't tell you why. You could, however, insert a group on class name and department fax and then suppress the group sections, and see if that helps maintain those sorts before the front end additional sorts.

-LB