Reseting SQL Errors

Monday 8 August 2011 @ 9:17 am

I have had several instances recently where a change to a report caused Crystal to generate strange SQL.  Sometimes you can see the problem under “Database > Set Location” where you will notice two different instances of the same database in the upper window.  Or, you may notice that your SQL Query gets separated into two or three independent queries when it should all be one query.  In a few cases the SQL gets so bad that it generates an error from the database, or it generates a “Cartesian product” result, trying to return all combinations of records between the tables.

If you run into a situation like this here are the steps that I have found that help:

1) Switch to design mode so that you don’t launch a query with every change.

2) Go into “Database >Set Location” and see if you have two different connections that should be one.  If so, set the location of both instances of the connection on top to the same connection on the bottom.  This might seem redundant since they appear the same in the window, but your goal is to make sure the top half of the “Set Location” window doesn’t show two instances of the same connection.  Set them both to the same connection below and you should see them consolidate into one list.

3) Go into Database > Show SQL Query and see if you have two different SELECT clauses that read data from the same connection.  If you do you might want to try step 2 again.

4) As a last resort you may need to remove a field or table from the report temporarily and then re-add it.  I find that helps to refresh the report before re-adding the table so that CR is forced to generate new SQL.

In one case I saw recently someone had removed a table and added another instead of using “Set Location”.  One field from the table they removed was still being used as a Group field in the report.  Rather than generating an error the report added a separate query to the SQL to pull that field from the original table.  Since that table was already removed there was no way to remove it again.  And changing the group to use the field from the new table didn’t reset the SQL, since the table and field names were the same.  Removing the group was only a temporary solution because adding that group back in from the new table we still caused the SQL to pull the field from the original table.

I finally solved it by grouping on another field from a different table (temporarily).  Then I wrote a formula to use as the group field, putting the field I wanted to group on into the formula.  This worked.  My guess is that the SQL was trying to convert the group field into an ORDER BY and that part wasn’t being reset correctly.  By using a formula I prevented CR from making the group into an ORDER BY.

So, if you experience any strange SQL behavior you can start by trying the above approaches.  If after those you are still stuck and you want a professional to take a look at what you are facing, give me a call.

Leave a Reply

Recrystallize Pro