KB40990: "column "c1" is of type numeric but expression is of type varchar. HINT: You will need to rewrite or cast the expression." error message appears when configuring a report into data mart in MicroStrategy Developer 9.x and 10.x against Vertica 5.0.

SYMPTOM:
 
When user configuring a report into data mart in Strategy Developer 9.x and 10.x against Vertica 5.0, an error message appears as shown below. 
 

"column "c1" is of type numeric but expression is of type varchar. HINT: You will need to rewrite or cast the expression."

• Create a new table named 'sjiao' in Vertica 5.0 database as shown below. Insert three columns c1, c2, c3 into the table and add 2 rows data to each column. The data type of c1, c2 and c3 are Decimal(10,2), Verchar(5) and date.
   
        

• In Strategy Desktop, create metric 'Sum of C1' corresponding to column c1 and create attribute c2, C3 corresponding to column c2 and c3. Add them to the report as shown below.
   
        

•  In report editor, go to Data -> Configure Data Mart. In 'Report Data Mart Setup' window, click table name 'sjiao' and choose option 'Appending to existing table', and then click 'OK'.
   
        

• Run report,  the error message will appear.

 
CAUSE:
 
The SQL to creating the data mart is actually as shown below:
 
 
      

 
 
 
 
Note: The pass0 is actually ignored.
 
In pass1, note that a11.c2  will be inserted into c1. The data type of c2 is Verchar(5) while the type of c1 is decimal(10,2) and therefore the data type mismatch.
 
 
 
 
 
 
WORKAROUND:

•  Use 'Create a new table' option when configuring data mart as shown below.
• Otherwise, modify the report level VLDB setting 'Select/Insert'->'Datamart Column Order' to 'Columns created in order in which they appear on the template' . But it will works for changing the order of attributes in a report template, e.g. this workaround cannot resolve the issue in the above example.