KB484397: "String data, right truncated" error is received when running a report that contains FFSQL and with Parameterized Query enabled

Description 

When Parameterized Query is enabled, the query is automatically converted to Parameterized Query, text prompts are input as parameters, and the executing SQL looks like the following:

select    a12.REGION_ID  REGION_ID,
    max(a13.REGION_NAME)  REGION_NAME0,
    a11.CALL_CTR_ID  CALL_CTR_ID,
    max(a12.CENTER_NAME)  CENTER_NAME,
    sum((a11.TOT_DOLLAR_SALES - a11.TOT_COST))  WJXBFS1,
    CASE WHEN sum((a11.TOT_DOLLAR_SALES - a11.TOT_COST)) < 1000 THEN 'LOW' ELSE ? END   WJXBFS2
from    city_ctr_sls    a11
    join    lu_call_ctr    a12  on (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
    join    lu_region    a13  on (a12.REGION_ID = a13.REGION_ID)
group by    a12.REGION_ID, a11.CALL_CTR_ID
with parameters: Test

In this case, the column

WJXBFS2

may return the value 'LOW' or the value of parameter, e.g., 'Test.' Thus, the column length should be the length of the larger value, 4, but the backend program, SQLDescribeColW function, does not return the expect length. For example, when against Redshift, the returned length is 3. When against SQL Server, the returned length is 999. In this situation, once the input parameter length is larger than the returned length, the report hits an error. 

Solution 

To correct the error, FFSQL, which is included in column expression, needs to use the CAST function to give the column a determined length, as the below SQL:
 

select    a12.REGION_ID  REGION_ID,
    max(a13.REGION_NAME)  REGION_NAME0,
    a11.CALL_CTR_ID  CALL_CTR_ID,
    max(a12.CENTER_NAME)  CENTER_NAME,
    sum((a11.TOT_DOLLAR_SALES - a11.TOT_COST))  WJXBFS1,
     cast(CASE WHEN sum((a11.TOT_DOLLAR_SALES - a11.TOT_COST)) < ? THEN 'LOW' ELSE ? END as varchar(200))   WJXBFS2
from    city_ctr_sls    a11
    join    lu_call_ctr    a12  on (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
    join    lu_region    a13  on (a12.REGION_ID = a13.REGION_ID)
group by    a12.REGION_ID, a11.CALL_CTR_ID