Converting Crystal formula logic into SQL queries

Friday 16 August 2013 @ 6:04 am

I recently had to convert a complex Crystal Report into a SQL Query, including all the calculations. I eventually found ways to convert everything that was needed and learned some valuable SQL skills. But I also learned to appreciate the things I take for granted in Crystal syntax, things that have no equivalent in SQL syntax. Here are some examples just in case someone else has to do a similar conversion:

1) One huge difference is how simple it is in Crystal to reference one expression in another. Crystal will automatically calculate a series of dependent formulas in the right order, based on which formulas refer to others. In a SQL query, if you write a calculation for column A and you want to use that as part of the calculation for the next column, you can’t simply refer to column A by name. Instead you have to repeat the entire calculation.

The exception is if you use subqueries. You can write a simple query that calculates expression A and then refer to that query in a higher level query. For some of my more complex formula chains it took 6 layers of nested subqueries to get the logic we needed. A simple example is shown in Item #2 below.

However, this highlights one advantage of SQL – you can nest as many levels as you want. And, any level can calculate summaries of the prior level. This allows you to do summaries of summaries of summaries, etc. In Crystal you get one pass for summaries, then one pass for variables and you are done.

2) My second example is somewhat related. SQL will not allow the result of an expression to be TRUE or FALSE because SQL Server doesn’t have a Boolean data type. The closest is a bit datatype which has a value of 1 or 0, but this isn’t the same because it can’t be used as boolean condition without a comparison.

I miss these because I often write Boolean formulas like this:

//Qualified
{Code} = “X” and {Status} = “A” and {Period} = 123

I can then refer to these Boolean formulas in other IF THEN statements, which makes the logic very cleary:

If {@Qualified} and {@OverDue}
then….

But in SQL the closest I could get to the first formula is something like this:

SELECT
CASE WHEN CAL.CODE = ‘X’ and CAL.STATUS = ‘A’ and CAL.Period = 123
THEN ‘True’ ELSE ‘False’ END as Qualified ….

Then if I wanted to refer to that expression in another expression have to use a subquery as mentioned above, like this:

Select
CASE WHEN A.Qualified = ‘True’ and A.DaysOver > 0
then ‘Qualified’ else ‘Not Qualified’ END as QualText
from ( SELECT
CAL.DaysOver,
CASE WHEN CAL.USERID = ‘KEN’ THEN ‘True’ ELSE ‘False’ END as Qualified
FROM CAL) A

3) Crystal has the ability to automatically set individual formulas to deal with the null values as if they were empty strings or zeros. It gets to be very cumbersome in SQL to check for NULL values in every part of every calculation.

4) Crystal has a a VAL() function which is very effective at turning numeric strings into numbers. In SQL you can use CAST or CONVERT but both of these will fail if the field has any non-numeric characters. The Val() function will just give a zero for a value that is not numeric and will even take the numeric portion from the beginning of a string (like 45B) and stop at the first alpha character. Val is much more forgiving than the CAST/Convert options.

5) Crystal has CASE logic, but I find IF THEN logic much clearer to read and follow. This is just my preference but I really miss using IF THEN statements in SQL.

I am sure I will think of more, but these are the ones that came to mind on this project

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)







Leave a Reply

Recrystallize Pro