Free-hand SQL Error Caused by Multiple Columns with Same Name

The return of free-hand SQL (a.k.a. free hand SQL, freehand SQL, FHSQL) within Web Intelligence (in 4.1 SP06 I think) opens up some new possibilities to query data in a way your existing universes may not support or on which you do not yet have a universe. It is not without its own set of issues such as being difficult to maintain and easy to introduce inefficient queries. Those issues are covered elsewhere. The intent of this blog post is to demystify one instance of the many generic errors we all see from time to time in BusinessObjects.

I built and refined my SQL statement in SQL Server Management Studio (SSMS). Here is simplified example of what I was trying to do.

The query was working great in SSMS. I successfully validated it within Web Intelligence. However, when I hit “OK” on the free-hand SQL text editor I got an error message saying, “A problem is encountered. The requested action cannot be completed.”

Not so helpful. At first I thought perhaps my query was too long (250 lines, 15000 characters), but then I had it happen again with a much shorter query (40 lines, 1500 characters). After much trial and error I finally determined the cause. I had more than one column with the same name. In my actual query I was returning 30 columns with some defined as CASE statements so it was not as obvious I had duplicate column name as it is in the example above.

If your query does not list out the columns explicitly (i.e. SELECT * …) and you are joining tables on columns that have the same name you will encounter this issue and it will be even harder to detect since the columns are not listed.

In conclusion, only use free-hand SQL as a last resort. If you must use free-hand SQL avoid “SELECT *” and explicitly list the columns in your query and make sure you do not use the same column name more than once.

Enjoy!

Noel