EIS Drill-through Issue With Stored Procedure

I worked with a user on the Essbase.org forum this week who was reporting an issue with EIS Drill-through. Specifically, the drill-through report used a stored procedure but the substitution tokens used to pass cell context information in EIS caused the procedure to throw an error. Here is the stored procedure:

exec essbase_detail_gldate_well $$COMPANY-VALUE$$, 'JAN 2008', 'W0077047', '0501.0005'

This format led to a confusing SQL error: Error -1: WHERE clause is missing in the SQL. Very strange. The stored procedure itself contained non-standard SQL and essentially passed the query over to the AS400 to do the actual query.

set @sql = "select * openquery(AS400PROD,''Select * from lib.file where glcono='+substring(@company,1,3)+ ' and glidno='+ substring(@well, 1,7)"

exec (sql)

Note that the user reported significant performance improvements when using this format and it worked when tested with hard-coded values instead of the EIS tokens, so it was important to try to make it work with the non-standard SQL.

I suggested that perhaps the issue was in parsing the SQL and that adding some conversion functions to properly type the token values being passed in may help. I don't exactly know how the user, Brian Howell, figured this out, but he tried putting a comment in the SQL that seems to have fooled EIS. It appears that EIS may look for the 'WHERE' keyword before starting to substitute tokens. In any case, here is the SQL that worked:

/* select * from where */

exec essbase_detail_gldate_well $$COMPANY-VALUE$$, 'JAN 2009', 'W0077407', '0501.0005'

Congratulations Brian on figuring that one out!