SQL expressions don’t get used very often. After all, if you know how to write a SQL expression, why not write the whole query as a command?  But this week a customer asked me if using a SQL expression could help them look up values in a master code table.  It was a great idea and we developed a handy SQL expression that they can use in many different areas.

This customer’s database stores all of their code descriptions in a single master lookup table. I see this quite often.  The code table will have different groups of records for different code fields.  There might be one set of records to decode the product category, another set to decode the customer type, and maybe even a set to decode the state field for addresses.  When you have multiple code sets in the same table you will usually have three fields:

• {Code} – the code value
• {Desc} – the description that goes with that code
• {Field} – the name of the field that is being decoded (e.g. State, ProdCat , CusType)

The last field is necessary when the codes are not unique. For instance a code of 007 might represent Ohio in the “State” list, while further down code 007 might represent ‘filters’ in the ProdCat list.  So once you link on the code you also have to add a record selection filter to specify the field.  Things get complicated if the code field is optional because Crystal doesn’t support filters on outer join fields.  But even when the code field is a required field, retrieving a description involves five steps for each code:

1) add a new instance of a table
2) link the new instance to the correct code field
3) change the alias of the table to make it clear which field is being decoded
4) add the appropriate filter for that field in the selection formula
5) place that field on the report.

With a SQL expression all of the above is contained within the expression. Here is an example:

(SELECT max(code.desc)
FROM code
WHERE code.field = 'ProdCat'
and po.ProdCat = code.code)

Once you verify that it works you can simply copy the object from the layout of one report and paste it in another report and you are done.  All the steps above are accomplished in that one step. And if you need another field decoded you just duplicate the field and change the value ‘ProdCat’ to another code.  Best of all you can use it with optional codes, because the filter is in the expression, not in the overall report query.

And, thanks to Laurie Weaver, a developer at Wyse Solutions, for initiating this idea.

Note –  in certain reports and in certain versions of Crystal a SQL Expression will error with:

“The multi-part identifier [your field] could not be bound.”

This is a Crystal bug and here is the work around.