Error itgensql005: unknown identifier 'ID'

Question

I'm using the Invantive Query Tool to update address data for suppliers in Exact Online on a large scale. I've written some VBA code that generates SQL code to put into the Invantive Query Tool. I've used this before and it worked fine, I've updated hundreds of Exact Online table values about 1 or 2 months ago. Now I wanted to use the same code to run a new update, however I get errors I didn't get before.

Let's start with the code that worked before:

use 1091919

UPDATE exactonlinerest..Accounts
SET    AddressLine1='Nieuw Walden 56-64'
WHERE  code like '% 500053'
AND    AddressLine1 != 'Nieuw Walden 56-64'
AND    division = 1091919

When I run this I get the error:

Unknown identifier: 'AddressLine1'." 

and get the suggestion to use "AddressLine1_".

So I add the underscore and it does seem to remove the error in finding AddressLine1, however I get a new error that reads:

Unknown identifier: 'ID'.

and I have no idea where it comes from. These are the error details:

UPDATE exactonlinerest..Accounts SET AddressLine1_='Nieuw Walden 56-64' WHERE code like '% 500053' AND AddressLine1_ != 'Nieuw Walden 56-64' AND division = 1091919


Type: Invantive.Configuration.ValidationException    
bij Invantive.Configuration.ValidationException..ctor(String errorCode,
String errorMessage, String kindRequest, String localStackTrace,
String nk, Exception innerException)    
bij Invantive.Sql.ExpressionParser.<>c__DisplayClass4_0.CreateExpressionParser>b__1(String qualifiedColumnName)    
bij Invantive.Data.ExtensionMethods.TryGetValue[TKey,TValue](Dictionary`2dictionary, TKey key, Func`2 customExceptionFunction)    
bij Invantive.Sql.ExpressionParser.<>c__DisplayClass4_0.<CreateExpressionParser>b__0(String name)    
bij Invantive.Sql.TransformToExpression.GetExpressionForField(String fieldName, Type& type)    
bij Invantive.Sql.TransformToExpression.GetProjectionForField(String fieldName, Type& type)    
bij Invantive.Sql.UpdateStatementTree.CompileInternal(ParameterList parameters, ExpressionParser expressionParser)    
bij Invantive.Sql.QueryTree.Compile(ParameterList parameters,ExpressionParser expressionParser)    
bij Invantive.Sql.QueryPlan.ForcePlan()    
bij Invantive.Sql.QueryPlan.get_Fields()    
bij Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlAction(String actionSql, ParameterList parameters, String& handlingPath)    
bij Invantive.Data.ConnectionManager.PassthroughSqlAction(String actionSql, ParameterList parameters)    
bij Invantive.Data.ActionProceduresBase.PassthroughSqlAction(String actionSql, ParameterList parameters)    
bij Invantive.Producer.Windows.Forms.QueryTool.ExecuteStatement(IProgressNotifier notifier, String statement, ParameterList bindVariables, Boolean showResultsInGrid, Boolean showStatistics, Boolean memorizeStatisticsInSqlHistory, Boolean allowPaging)    
bij Invantive.Producer.Windows.Forms.QueryTool.FetchResultsFromSql()   
bij Invantive.Producer.Windows.Forms.QueryTool.<>c__DisplayClass135_0.<FetchData>b__0()
bij System.Threading.ThreadHelper.ThreadStart_Context(Object state)   
bij System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)    
bij System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)    
bij System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)    
bij System.Threading.ThreadHelper.ThreadStart()    
bij Invantive.Data.ExtensionMethods.TryGetValue[TKey,TValue](Dictionary`2 dictionary, TKey key, Func`2 customExceptionFunction) in File216:regel 1601    
bij Invantive.Sql.ExpressionParser.<>c__DisplayClass4_0.<CreateExpressionParser>b__0(String name) in File118:regel 53    
bij Invantive.Sql.TransformToExpression.GetExpressionForField(String fieldName, Type& type) in File115:regel 826    
bij Invantive.Sql.TransformToExpression.GetProjectionForField(String fieldName, Type& type) in File115:regel 878    
bij Invantive.Sql.UpdateStatementTree.CompileInternal(ParameterList parameters, ExpressionParser expressionParser) in File112:regel 132   
bij Invantive.Sql.QueryTree.Compile(ParameterList parameters, ExpressionParser expressionParser) in File131:regel 91    
bij Invantive.Sql.QueryPlan.ForcePlan() in File130:regel 86    
bij Invantive.Sql.QueryPlan.get_Fields() in File130:regel 63    
bij Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlAction(String actionSql, ParameterList parameters, String& handlingPath) in File39:regel 3754
--- Einde van stacktracering vanaf vorige locatie waar uitzondering is opgetreden ---    
bij System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    
bij Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlAction(String actionSql, ParameterList parameters, String& handlingPath) in File39:regel 3789    
bij Invantive.Data.ConnectionManager.PassthroughSqlAction(String actionSql, ParameterList parameters) in File39:regel 1928
--- Einde van stacktracering vanaf vorige locatie waar uitzondering is opgetreden ---    
bij System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    
bij Invantive.Data.ConnectionManager.PassthroughSqlAction(String actionSql, ParameterList parameters) in File39:regel 1940    
bij Invantive.Data.ActionProceduresBase.PassthroughSqlAction(String actionSql, ParameterList parameters) in File29:regel 212    
bij Invantive.Producer.Windows.Forms.QueryTool.ExecuteStatement(IProgressNotifier notifier, String statement, ParameterList bindVariables, Boolean showResultsInGrid, Boolean showStatistics, Boolean memorizeStatisticsInSqlHistory, Boolean allowPaging) in File934:regel 2876    
bij Invantive.Producer.Windows.Forms.QueryTool.FetchResultsFromSql() in  File934:regel 2459

I'd appreciate it very much if this error can be fixed.

Answer 1

This ended up to be a bug in the evaluation in the SQL engine.

Please let me know if it still happens to you.