"updateDatabase throws Incorrect syntax near the keyword WHERE error"

ssarkar97

i have a simple process that reads a bunch of columns from a database table and finds outlier.
Then i wan to update the same table with the outlier column. The record match should be based on the ID column.
The ID column should play no role in outlier detection. It only exists to help update the right record.

With the process below, i am getting Incorrect syntax near the keyword WHERE" error when viewing results.
What am I doing wrong?

<code>
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<process version="5.2.008">
 <context>
   <input/>
   <output/>
   <macros/>
 </context>
 <operator activated="true" class="process" compatibility="5.2.008" expanded="true" name="Process">
   <process expanded="true" height="349" width="680">
     <operator activated="true" class="read_database" compatibility="5.2.008" expanded="true" height="60" name="Read Database" width="90" x="45" y="75">
       <parameter key="connection" value="windev"/>
       <parameter key="query" value="select id, insured_age,city,claim_amount  from mvi_claims_data"/>
       <parameter key="table_name" value="BRANCH_DIM_SS_TEST25"/>
       <enumeration key="parameters"/>
     </operator>
     <operator activated="true" class="detect_outlier_distances" compatibility="5.2.008" expanded="true" height="76" name="Detect Outlier (Distances)" width="90" x="246" y="75"/>
     <operator activated="true" class="update_database" compatibility="5.2.008" expanded="true" height="60" name="Update Database" width="90" x="430" y="60">
       <parameter key="connection" value="windev"/>
       <parameter key="table_name" value="mvi_claims_data"/>
     </operator>
     <connect from_op="Read Database" from_port="output" to_op="Detect Outlier (Distances)" to_port="example set input"/>
     <connect from_op="Detect Outlier (Distances)" from_port="example set output" to_op="Update Database" to_port="input"/>
     <connect from_op="Update Database" from_port="through" to_port="result 1"/>
     <portSpacing port="source_input 1" spacing="0"/>
     <portSpacing port="sink_result 1" spacing="0"/>
     <portSpacing port="sink_result 2" spacing="0"/>
   </process>
 </operator>
</process>

</code>

awchisholm

Hello

You need to set the role (use Set Role) of the id attribute to id before the outlier detection to avoid it being used as part of the outlier determination.

Does the target table already have a column called outlier?

In the Update Database operator, use the select attributes button to select only id and outlier - this might help.


regards

Andrew

ssarkar97

Thanks, Andrew.
Yes, the target table does have a column called outlier.

i am using the operator: update Database
Its documentation says there's a parameter called
db id attribute name: The name of the id attribute in the database and the ExampleSet' Range: string; default: 'id'

However, i am not seeing any option to provide this input (see screenshot).
http://nbstoragemahesh.blob.core.windows.net/jbsbsorba/rm.png


regards,
Sumant

awchisholm

Hello

If you select "attribute filter type" within the Update Database operator, you can select the attributes you want to use. Id and outlier are the minimum - make sure you check "include special attributes"

regards

Andrew

ssarkar97

Thanks once again, Andrew.
I am using the select "attribute filter type" option. I need all the columns (including id and outlier).

Still that "Incorrect syntax near the keyword WHERE" error.

I notice that i am not seeing any option to set this parameter:
db id attribute name: The name of the id attribute in the database and the ExampleSet' Range: string; default: 'id'

When I switch to Expert mode, i notice a message that says, "1 hidden expert parameter". I suspect this is the parameter that needs to be set. But can't find anyway to set it.
http://nbstoragemahesh.blob.core.windows.net/jbsbsorba/rm2.png

awchisholm

Hello

I can't find the parameter but I found it didn't matter. Select the id and outlier attributes (make sure you check "include special attributes" or it won't work) and the database table will be updated. If you include all attributes, the error happens.

regards

Andrew

ssarkar97

unfortunately, i still get that error.. (i am using subset of attributes... and the subset has id, outlier only).
As a workaround, i have used 'write Database' operator.
So i write into a new table.
Then using an external T-SQL process, i am updating the original table.
Will continue trying to make the original process work.

Marco_Boeck

Hi,

due to some limitations currently the Update Database operator "id" parameter is actually the attribute selector. The column(s) selected there will be used in the WHERE clause, all other columns will be updated in the DB.

Regards,
Marco