Я делаю финансовый отчет в Excel, используя данные из Exact Online с помощью надстройки Invantive Control Excel. В Exact Online у меня есть классификация для Главной бухгалтерской книги. В моем файле Excel я хотел бы сопоставить эти классификации в своем графике отчетности. Поэтому я сделал таблицу с сопоставлением классификаций Exact Online и классификаций отчетности. Используя надстройку Invantive Control для Excel, я ввел следующий SQL-запрос:
select periods_year_reportingyear_attr
, reportingperiod_attr
, division_code
, division_hid
, division_name
, periods_year_years_balance_code_attr
, periods_year_years_balance_description
, '=I_EOL_GL_ACTCLN_CODE($C{E,.,.,^+2,.}, $C{E,.,.,^+5,.})' verdichting_code
, '=I_EOL_GL_ACTCLN_DESCRIPTION($C{E,.,.,^+2,.}, $C{E,.,.,^+5,.})' verdichting_naam
, '=i_eol_bal_year_open($C{E,.,.,^+2,.} , $C{E,.,.,^,.}, $C{E,.,.,^+5,.}) + if($C{E,.,.,^+1,.} = 1, 0, i_eol_bal_pder($C{E,.,.,^+2,.},$C{E,.,.,^,.},1,$C{E,.,.,^+1,.}-1,$C{E,.,.,^+5,.}))' startsaldo
, balance
, '=i_eol_bal_year_open($C{E,.,.,^+2,.} , $C{E,.,.,^,.}, $C{E,.,.,^+5,.}) + i_eol_bal_pder($C{E,.,.,^+2,.},$C{E,.,.,^,.},1,$C{E,.,.,^+1,.},$C{E,.,.,^+5,.})' eindsaldo
, periods_year_years_balance_balancetype_attr
from balancelinesperperiod
order
by periods_year_reportingyear_attr
, reportingperiod_attr
, division_hid
, periods_year_years_balance_code_attr
, '=INDEX(tab_reporting[Reporting];MATCH(NUMBERVALUE($C{E,.,.,^+7,.});tab_reporting[GL Class - Code];0))'
В которой tab_reporting — это имя таблицы с отображением классификаций отчетности и Exact Online. Эта таблица находится на другой вкладке в Excel. Могу выслать файл Excel по запросу. Эта последняя строка запроса приводит к следующему сообщению об ошибке:
Синтаксическая ошибка между двумя символами '*' в строке 20, столбце 7: выберите period_year_re...ance_code_attr, *'=INDEX(tab_reporting[Reporting];MATCH(NUMBERVALUE($C{E,., .,^+7,.});tab_reporting[GLClass - Code];0))'*** Ошибка: нет приемлемой альтернативы на входе '=INDEX(tab_reporting[Reporting];MATCH(NUMBERVALUE($C{E,. ,.,^+7,.}); tab_reporting[GLClass - Код];0))''
и более подробно:
itgensql056: Syntax error between the two '***' on line 20, column 7:select periods_year_re...ance_code_attr<LF>, ***'=INDEX(tab_reporting[Reporting];MATCH(NUMBERVALUE($C{E,.,.,^+7,.});tab_reporting[GL Class - Code];0))'***Error: no viable alternative at input ''=INDEX(tab_reporting[Reporting];MATCH(NUMBERVALUE($C{E,.,.,^+7,.});tab_reporting[GL Class - Code];0))''select periods_year_reportingyear_attr, reportingperiod_attr, division_code, division_hid, division_name, periods_year_years_balance_code_attr, periods_year_years_balance_description, '=I_EOL_GL_ACTCLN_CODE($C{E,.,.,^+2,.}, $C{E,.,.,^+5,.})' verdichting_code, '=I_EOL_GL_ACTCLN_DESCRIPTION($C{E,.,.,^+2,.}, $C{E,.,.,^+5,.})' verdichting_naam, '=i_eol_bal_year_open($C{E,.,.,^+2,.} , $C{E,.,.,^,.}, $C{E,.,.,^+5,.}) + if($C{E,.,.,^+1,.} = 1, 0, i_eol_bal_pder($C{E,.,.,^+2,.},$C{E,.,.,^,.},1,$C{E,.,.,^+1,.}-1,$C{E,.,.,^+5,.}))' startsaldo, balance, '=i_eol_bal_year_open($C{E,.,.,^+2,.} , $C{E,.,.,^,.}, $C{E,.,.,^+5,.}) + i_eol_bal_pder($C{E,.,.,^+2,.},$C{E,.,.,^,.},1,$C{E,.,.,^+1,.},$C{E,.,.,^+5,.})' eindsaldo, periods_year_years_balance_balancetype_attrfrom balancelinesperperiod order by periods_year_reportingyear_attr, reportingperiod_attr, division_hid, periods_year_years_balance_code_attr, '=INDEX(tab_reporting[Reporting];MATCH(NUMBERVALUE($C{E,.,.,^+7,.});tab_reporting[GL Class - Code];0))'Type: Invantive.Data.ValidationException at Invantive.Data.ValidationException..ctor(String messageCode, String messageText, String kindRequest, String localStackTrace, String nk, Exception innerException) at Invantive.Data.InvantiveParserErrorListener.SyntaxError(IRecognizer recognizer, IToken offendingSymbol, Int32 line, Int32 charPositionInLine, String msg, RecognitionException triggeringException) at Antlr4.Runtime.ProxyErrorListener`1.SyntaxError(IRecognizer recognizer, Symbol offendingSymbol, Int32 line, Int32 charPositionInLine, String msg, RecognitionException e) at Antlr4.Runtime.Parser.NotifyErrorListeners(IToken offendingToken, String msg, RecognitionException e) at Antlr4.Runtime.DefaultErrorStrategy.NotifyErrorListeners(Parser recognizer, String message, RecognitionException e) at Antlr4.Runtime.DefaultErrorStrategy.ReportNoViableAlternative(Parser recognizer, NoViableAltException e) at Antlr4.Runtime.DefaultErrorStrategy.ReportError(Parser recognizer, RecognitionException e) at Invantive.Sql.InvantiveSQLParser.column() at Invantive.Sql.InvantiveSQLParser.sortedColumnList() at Invantive.Sql.InvantiveSQLParser.sortedColumnList() at Invantive.Sql.InvantiveSQLParser.sortedColumnList() at Invantive.Sql.InvantiveSQLParser.sortedColumnList() at Invantive.Sql.InvantiveSQLParser.sortedColumnList() at Invantive.Sql.InvantiveSQLParser.orderBy() at Invantive.Sql.InvantiveSQLParser.selectStatement() at Invantive.Sql.InvantiveSQLParser.sqlStatement() at Invantive.Sql.InvantiveSQLParser.sqlBatch() at Invantive.Sql.SqlEngine.ParseStatement(String sqlStatement, Boolean allowSelect) at Invantive.Sql.SqlEngine.Execute(IProviderManager manager, String sqlStatement, ParameterList parameters, Boolean allowSelect) at Invantive.Sql.SqlEngine.ExecuteAndFetch(IProviderManager manager, String sqlStatement, ParameterList parameters, Boolean allowSelect) at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(String actionSql, ParameterList parameters, String& handlingPath) at Invantive.Data.ConnectionManager.PassthroughSqlActionTable(String actionSql, ParameterList parameters) at Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(String actionSql, ParameterList parameters) at Invantive.Producer.Windows.Forms.FactsForm.SetDataSource(String sqlQuery, ParameterList parameters, String objectName) at Invantive.Producer.Control.Editors.BlockFactsEdit.showFactsButton_Click(Object sender, EventArgs e) at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.Application.ThreadContext.LocalModalMessageLoop(Form form) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.RunDialog(Form form) at System.Windows.Forms.Form.ShowDialog(IWin32Window owner) at System.Windows.Forms.Form.ShowDialog() at Invantive.Producer.Control.Utility.OpenRepositoryEditor(Workbook workbook) at Invantive.Producer.Control.ActionsRibbon.editModelButton_Click(Object sender, RibbonControlEventArgs e) at Microsoft.Office.Tools.Ribbon.RibbonPropertyStorage.ControlActionRaise(IRibbonControl control) at Microsoft.Office.Tools.Ribbon.RibbonPropertyStorage.ButtonClickCallback(RibbonComponentImpl component, Object[] args) at Microsoft.Office.Tools.Ribbon.RibbonManagerImpl.Invoke(RibbonComponentCallback callback, Object[] args) at Microsoft.Office.Tools.Ribbon.RibbonMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at Microsoft.Office.Tools.Ribbon.RibbonManagerImpl.System.Reflection.IReflect.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParameters) at Invantive.Data.InvantiveParserErrorListener.SyntaxError(IRecognizer recognizer, IToken offendingSymbol, Int32 line, Int32 charPositionInLine, String msg, RecognitionException triggeringException) in File104:line 122 at Antlr4.Runtime.ProxyErrorListener`1.SyntaxError(IRecognizer recognizer, Symbol offendingSymbol, Int32 line, Int32 charPositionInLine, String msg, RecognitionException e) at Antlr4.Runtime.Parser.NotifyErrorListeners(IToken offendingToken, String msg, RecognitionException e) at Antlr4.Runtime.DefaultErrorStrategy.NotifyErrorListeners(Parser recognizer, String message, RecognitionException e) at Antlr4.Runtime.DefaultErrorStrategy.ReportNoViableAlternative(Parser recognizer, NoViableAltException e) at Antlr4.Runtime.DefaultErrorStrategy.ReportError(Parser recognizer, RecognitionException e) at Invantive.Sql.InvantiveSQLParser.column() in File59:line 1649 at Invantive.Sql.InvantiveSQLParser.sortedColumnList() in File59:line 1571 at Invantive.Sql.InvantiveSQLParser.sortedColumnList() in File59:line 1585 at Invantive.Sql.InvantiveSQLParser.sortedColumnList() in File59:line 1585 at Invantive.Sql.InvantiveSQLParser.sortedColumnList() in File59:line 1585 at Invantive.Sql.InvantiveSQLParser.sortedColumnList() in File59:line 1585 at Invantive.Sql.InvantiveSQLParser.orderBy() in File59:line 1398 at Invantive.Sql.InvantiveSQLParser.selectStatement() in File59:line 491 at Invantive.Sql.InvantiveSQLParser.sqlStatement() in File59:line 366 at Invantive.Sql.InvantiveSQLParser.sqlBatch() in File59:line 283 at Invantive.Sql.SqlEngine.ParseStatement(String sqlStatement, Boolean allowSelect) in File35:line 652 at Invantive.Sql.SqlEngine.Execute(IProviderManager manager, String sqlStatement, ParameterList parameters, Boolean allowSelect) in File35:line 552 at Invantive.Sql.SqlEngine.ExecuteAndFetch(IProviderManager manager, String sqlStatement, ParameterList parameters, Boolean allowSelect) in File35:line 513 at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(String actionSql, ParameterList parameters, String& handlingPath) in File73:line 4499--- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(String actionSql, ParameterList parameters, String& handlingPath) in File73:line 4537 at Invantive.Data.ConnectionManager.PassthroughSqlActionTable(String actionSql, ParameterList parameters) in File73:line 2357--- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Invantive.Data.ConnectionManager.PassthroughSqlActionTable(String actionSql, ParameterList parameters) in File73:line 2369 at Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(String actionSql, ParameterList parameters) in File63:line 134 at Invantive.Producer.Windows.Forms.FactsForm.SetDataSource(String sqlQuery, ParameterList parameters, String objectName) in File604:line 82 at Invantive.Producer.Control.Editors.BlockFactsEdit.showFactsButton_Click(Object sender, EventArgs e) in File180:line 189Invantive Control for Excel (stable-20161021-2025-ge3e5e61 Prod, L162135034)
