Outer join escape sequences

As part of a new project I’ve inherited a bunch of mission-critical reports currently being done in an ancient version of Crystal reports and the client would like to update them to SSRS.

I tend to prefer not just replicating the past but rather take it as an opportunity to learn from the old ways and do things more efficiently and helpfully. For example, sometimes you just can’t avoid a report that is pages and pages of data but why not eliminate that and replace it with an exception report if you are able to? If you can add some subtle highlighting to help people do their job better and save their eyes from the strain of trying to manually parse the data then I will do just that.

Ideally you want to take a big picture view of the project but sometimes that can be overwhelming, and it’s certainly better to work agile and with rapid iterations so to begin with I am just diving in to the first report with the aim of providing a first prototype within a couple of hours. This will enable me to get rapid feedback and avoid working towards something that isn’t wanted. As has been well discussed elsewhere, people don’t really know what they want so it helps to show them what is possible, and then you can help them towards the real objective of the reporting.

Anyway, that’s not what this post is about so to the issue. Apparently ODBC supports SQL-92 syntax for outer joins, but this was implemented using escape sequences in old versions of Crystal reports. I think I may have seen these before but they are still weird.

It looks like

Select some columns from
{oj table LEFT|RIGHT|OUTER JOIN table 2 on conditions}

So that’s fair enough, but the first report I’m looking at has this:

 

{oj table INNER JOIN table 2 on conditions}

So that makes no sense. Why would an {oj} escape sequence then have an INNER JOIN in it?

Apparently it’s a bug in Crystal

Crystal Reports may generate incorrect ODBC nested join escape syntax like follows

SELECT x FROM {oj (table1 synonym1 INNER JOIN table2 synonym2 ON x = y)  INNER JOIN table3 synonym3 ON y = z) }

 

 

edit: I’ve since realised that this syntax is required for using outer joins in ODBC, because modern versions of BIDS still create this syntax when using an ODBC connected datasource, but the above example is still a bug, albeit in ancient software.

Leave a Reply

Your email address will not be published. Required fields are marked *