KB12668: Using ’*’ (asterisk) as the wildcard character for pattern matching works in Microsoft Access but not in MicroStrategy Developer 9.4.1 and 10.x reports

SYMPTOM
When reports using attributes created from Logical Views are executed, they do not return any data, whereas, the SQL generated returns data directly in Microsoft Access. Certain reports return the ODBC error, 'Too few parameters. Expected 1.'
 
STEPS TO REPRODUCE

• Create a table called 'LOGICAL_VIEW_TEST' in the Microsoft Access database, used as the Strategy Tutorial project warehouse, as shown below:
  

• Bring the table into the project using the Warehouse Catalog.
• Create a logical view on top of this table to extract from the GEO column any value beginning with 'R', as shown below:
  

•  
  The SQL used is as follows:
  

  
  SELECT * FROM LOGICAL_VIEW_TEST WHERE GEO like 'R*'

  
   
  
• Define an attribute called 'Region' on top of this, and point only to this table, as shown below:
  

• Execute a report with just the 'Region' attribute created in the previous steps and notice that no data is retuned, as shown below:
  

•  
  

  
  QueryEngine encountered error: Execute Query failed: Odbc error. Odbc operation attempted: SQLExecDirect. Too few parameters. Expected 1.

  
   
  
• The following SQL is generated:
  

  
  select a11. AS GEO from (SELECT * FROM LOGICAL_VIEW_TEST WHERE GEO like 'R*' ) a11

  
   
  
• Running the SQL used to create the Logical View in the Query tool in Microsoft Access returns the expected data.
  

CAUSE
Microsoft Access uses the '%' (percentage) sign as the wildcard character for pattern matching and not the '*' (asterisk).
 
ACTION
Redefine the Logical View using the following SQL, replacing the asterisk with the percent sign, as shown below:

SELECT * FROM LOGICAL_VIEW_TEST WHERE GEO like 'R%'