I am trying to declare variable in SQL statement and try to execute it via ODBC C# driver(System.Data.Odbc 8.0.0) but it fails with parse syntax error.
When I try the same query on SQL editor, it runs.
Query:
DECLARE VARIABLE users_core__user_name_last = 'Young';
DECLARE VARIABLE users_core__user_name_last1 = 'Watson';
DECLARE VARIABLE users_core__user_name_last2 = 'Scott';
DECLARE VARIABLE users_core__user_name_last3 = 'Rogers';
DECLARE VARIABLE users_core__user_name_last4 = 'Smith%';
SELECT DISTINCT users_core359.`user_name_first` AS `User_First_Name_21_70`
,users_core359.`user_name_last` AS `User_Last_Name_22_70`
,users_core359_96.`user_name_first` AS `User_First_Name_21_96`
,users_core359_96.`user_name_last` AS `User_Last_Name_22_96`
FROM users_core users_core359
LEFT JOIN users_core users_core359_96 ON users_core359.`user_mgr_id` = users_core359_96.`user_id`
WHERE (
users_core359.`user_name_last` IN (
users_core__user_name_last
,users_core__user_name_last1
,users_core__user_name_last2
,users_core__user_name_last3
)
)
OR (users_core359_96.`user_name_last` LIKE users_core__user_name_last4) LIMIT 1000
Below is the error.
Message: ERROR [42000] [Simba][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: [PARSE_SYNTAX_ERROR] Syntax error at or near 'DECLARE': extra input 'DECLARE'. SQLSTATE: 42601 (line 1, pos 56) == SQL == DECLARE VARIABLE users_core__user_name_last = 'Young'; DECLARE VARIABLE users_core__user_name_last1 = 'Watson'; DECLARE VARIABLE users_core__user_name_last2 = 'Scott'; DECLARE VARIABLE users_core__user_name_last3 = 'Rogers'; DECLARE VARIABLE users_core__user_name_last4 = 'Smith%';SELECT DISTINCT users_core359.`user_name_first` AS `User_First_Name_21_70`, users_core359.`user_name_last` AS `User_Last_Name_22_70`, users_core359_96.`user_name_first` AS `User_First_Name_21_96`, users_core359_96.`user_name_last` AS `User_Last_Name_22_96` FROM users_core users_core359 LEFT JOIN users_core users_core359_96 ON users_core359.`user_mgr_id` = users_core359_96.`user_id` WHERE (users_core359.`user_name_last` IN (users_core__user_name_last, users_core__user_name_last1, users_co[Simba][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: [PARSE_SYNTAX_ERROR] Syntax error at or near 'DECLARE': extra input 'DECLARE'. SQLSTATE: 42601 (line 1, pos 56) == SQL == DECLARE VARIABLE users_core__user_name_last = 'Young'; DECLARE VARIABLE users_core__user_name_last1 = 'Watson'; DECLARE VARIABLE users_core__user_name_last2 = 'Scott'; DECLARE VARIABLE users_core__user_name_last3 = 'Rogers'; DECLARE VARIABLE users_core__user_name_last4 = 'Smith%';SELECT DISTINCT users_core359.`user_name_first` AS `User_First_Name_21_70`, users_core359.`user_name_last` AS `User_Last_Name_22_70`, users_core359_96.`user_name_first` AS `User_First_Name_21_96`, users_core359_96.`user_name_last` AS `User_Last_Name_22_96` FROM users_core users_core359 LEFT JOIN users_core users_core359_96 ON users_core359.`user_mgr_id` = users_core359_96.`user_id` WHERE (users_core359.`user_name_last` IN (users_core__user_name_last, users_core__user_name_last1, users_co, Stack Trace: at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, SQLRETURN retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
