KB35943: "Incorrect syntax near the keyword "in"" error message appears when executing a report with an attribute qualification filter containing a "Between" operator and embedded with attribute element prompts

SYMPTOM:
When executing a report with an attribute qualification filter containing a "Between" operator and embedded with attribute element prompts, the below error appears:
 
 
Error: SQL Generation Complete
QueryEngine encountered error: Execute Query failed.
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. Incorrect syntax near the keyword 'in'.
 
STEPS TO REPRODUCE:
Follow the steps below to reproduce the issue with Strategy Tutorial Project:
 

1. create a attribute element list prompt against attribute Year, save it as "prompt1", duplicate "prompt1" and rename it as "prompt2";
2. Create a report with attribute Year in rows, create a Attribute Qualification filter against attribute Year, qualify on Year ID with operator "Between", add prompts created at step1 in the filter, as shown in the image blow:

• Save the filter and run the report. When prompts are resolved, the report fails with the error showing in below:

 
Error: SQL Generation Complete
QueryEngine encountered error: Execute Query failed.
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. Incorrect syntax near the keyword 'in'.
CAUSE:
This is expected behavior.
 
Below is the report SQL for the above report. When attribute element list prompt is used in the report, SQL Engine will generate an IN clause, however it conflicts with the Between operator in report filter which caused the report failed to run.
 
select a11.YEAR_ID YEAR_ID from LU_YEAR a11 where a11.YEAR_ID between a11.YEAR_ID in (2007) and a11.YEAR_ID in (2010)
 
ACTION:
Starting from Strategy 9.0.0, element list prompts can be used in database passthrough functions, e.g. ApplyComparison.
 
Follow the below steps to create two report filters with ApplyComparison query to eliminate the error:
 

1. Create two Advanced Qualification report filters with custom queries as shown in the image below:

 
ApplyComparison ("#0 in (select year_id from lu_year where year_id >= #E1)", Year@ID, ?)
ApplyComparison ("#0 in (select year_id from lu_year where year_id >= #E1)", Year@ID, ?)
 
 
select a11.YEAR_ID  YEAR_ID from LU_YEAR a11 where (a11.YEAR_ID in (select year_id from lu_year where year_id >= 2010)and a11.YEAR_ID in (select year_id from lu_year where year_id >= 2007)) 
For details regarding how to use element list prompts in database passthrough functions, refer tot the below technical note:

• When resolving the prompts, the report can be executed correctly with below report SQL:
  
• KB32365: New feature in Strategy SQL Generation Engine 9.0.0: Use of element list prompts in database passthrough functions