Als ik 1 formule gebruik in een query, dan werkt het goed (de eerste hieronder). Echter, als ik een van de andere drie activeer, dan krijg ik een Out-of-Memory met versie 22.0.433 (64-bits versie van Excel):
itgenclr007: Application is using more memory than available. Restarting the application, use 64-bit Windows and Office or making more memory available might resolve this problem.
Onvoldoende geheugenbronnen beschikbaar om deze opdracht te voltooien. (Uitzondering van HRESULT: 0x8007000E (E_OUTOFMEMORY)).
An unknown error occurred.
Message ID: 91331dbd-6a3f-42b6-811e-b3de2dc94143
Occurred (UTC): 11-11-2022 15:34:04
System.OutOfMemoryException
System.Exception
bij System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
bij Microsoft.Office.Interop.Excel.Range.set_Value(Object RangeValueDataType, Object )
bij Invantive.Producer.Control.Utility.SetExcelRangeValues(GlobalState owner, ExecutionOptions executionOptions, ModelCache modelCache, Workbook workbook, Range range, Object[,] values) in c:\smoke\ws-22.0\Invantive.Control\src\Invantive.Producer.Control\Utility.cs:regel 9109
bij Invantive.Producer.Control.SyncToDatabaseForm.SyncDownloadRows(GlobalState owner, ExecutionOptions executionOptions, SystemWorkingContext context, iea_blocks_v block, Int32 numberOfRows, Int32 fakeRowCount, Int32 numberOfColumns, ResultSet resultTable, Decimal step, Int32 currentBlock, Int32 idColumnNumber, Int32 transactionColumnNumber, Point3d dataStartPoint, Dictionary`2 additionalSelectFields, Dictionary`2 fieldsByNameDictionary, Dictionary`2 columnBeginAndEndPoints, Dictionary`2 blocksByCodeDictionary, List`1 fields, Dictionary`2 blockDimensions, ConcurrentBag`1 expressionFieldForCheckPendingChange, Cube innerBorder) in c:\smoke\ws-22.0\Invantive.Control\src\Invantive.Producer.Control\ThisAddIn.cs:regel 1056
bij Invantive.Producer.Control.SyncToDatabaseForm.SyncDownload(GlobalState owner, ExecutionOptions executionOptions, DoWorkEventArgs e) in c:\smoke\ws-22.0\Invantive.Control\src\Invantive.Producer.Control\ThisAddIn.cs:regel 888
Query:
select t.Division
, t.Description
, t.GLAccountCode
, t.GLAccountDescription
, t.GLSchemeCode
, t.ClassificationCode
, t.ClassificationDescription
, t.PERIODS_YEAR_REPORTINGYEAR_ATTR
, t.REPORTINGPERIOD_ATTR
, t.PERIODS_YEAR_OPEN
, t.BALANCE
, t.BALANCE_CUM
, t.Mapping
, t.BW
, '=IF($C{E,.,.,^+5,.}="","",IF($C{E,.,.,^+5,.}="missing",XLOOKUP($C{E,.,.,^+3,.},Mapping_RGS!$B$3:$B$2803,Mapping_RGS!$C$3:$C$2803,XLOOKUP($C{E,.,.,^+2,.},Mapping_RGS!$A$3:$A$2803,Mapping_RGS!$C$3:$C$2803,$C{E,.,.,^+5,.},0,1),0,1),$C{E,.,.,^+5,.}))' classificatiesuggestie
, '=IF($C{E,.,.,^+5,.}="";"",IF($C{E,.,.,^+6,.}="missing",XLOOKUP($C{E,.,.,^+3,.},Mapping_RGS!$B$3:$B$2803,Mapping_RGS!$D$3:$D$2803,XLOOKUP($C{E,.,.,^+2,.},Mapping_RGS!$A$3:$A$2803,Mapping_RGS!$D$3:$D$2803,$C{E,.,.,^+6,.},0,1),0,1),$C{E,.,.,^+6,.}))' classificatieomschrijving
, '=IF($C{E,.,.,^+5,.}="";"",IF($C{E,.,.,^+12,.}="missing",XLOOKUP($C{E,.,.,^+3,.},Mapping_RGS!$B$3:$B$2803,Mapping_RGS!$E$3:$E$2803,XLOOKUP($C{E,.,.,^+2,.},Mapping_RGS!$A$3:$A$2803,Mapping_RGS!$E$3:$E$2803,$C{E,.,.,^+12,.},0,1),0,1),$C{E,.,.,^+12,.}))' mappingsuggestie
, '=IF($C{E,.,.,^+5,.}="";"",IF($C{E,.,.,^+13,.}="missing",XLOOKUP($C{E,.,.,^+3,.},Mapping_RGS!$B$3:$B$2803,Mapping_RGS!$F$3:$F$2803,XLOOKUP($C{E,.,.,^+2,.},Mapping_RGS!$A$3:$A$2803,Mapping_RGS!$F$3:$F$2803,$C{E,.,.,^+13,.},0,1),0,1),IF($C{E,.,.,^+13,.}="M",LEFT($C{E,.,.,^+14,.},1),$C{E,.,.,^+13,.})))' bwmapping
from importdata@inmemorystorage t
--
-- Alleen opnemen als er beginsaldo was, een mutatie en/of eindsaldo.
--
where ( coalesce(PERIODS_YEAR_OPEN, 0) !=0 or coalesce(BALANCE, 0) != 0 or coalesce(BALANCE_CUM, 0) != 0)