How to change the order of the links in Crystal

Tuesday 14 April 2015 @ 10:06 pm

Crystal added a feature 7 years ago, and this week I had to use it for the first time. I was working with a Pervasive database and testing how different join configurations would perform. After one change the connection started throwing an error that the SQL was invalid. The configuration in the LINKS window looked identical to another report that ran fine. But when I compared the SQL of the two reports I found that although the tables were linked the same, the SQL statements were adding the tables/joins in a different order. I needed a way to tell CR to do the same joins, but in a different order.

That is when I remembered a feature introduced in CR 2008. Whenever you have more than one join (3+ tables) you can right click in the background of the linking window and select “Order Links”. This allows you to specify the order in which the tables are incorporated into the “FROM” clause of the SQL statement. Normally the link order takes care of itself, based on the direction you drag the link lines. But in this case it appeared that the only way to get the SQL to work was to have the one Outer Join we needed as the very last join in the FROM clause. Sure enough, when I moved that link to the bottom of the order, the SQL ran without error.

Leave a Reply

Recrystallize Pro