Itgenoda462: No URLs generated for filters on table 'ExactOnlineREST.Logistics.SalesItemPrices'

Vragen
,
1

Sinds de update naar Querytool versie 22.0.480 PROD krijg ik de volgende foutmelding:

itgenoda462:
Statement #11 ‘Select lsi.it…pplier = true;’ beginnend op regel 17 veroorzaakte een fout.
No URLs generated for filters on table ‘ExactOnlineREST.Logistics.SalesItemPrices’.
Bericht ID: e13445cb-5376-424c-866c-502646e68438
Opgetreden (UTC): 2-1-2023 21:00:46

Query:

Select lsi.itemcode
    label 'Code'
,   lsi.itemdescription
    label 'Omschrijving'
,   lsi.purchaseunit
    label 'Inkoop Eenheid'
,   lsi.supplierdescription 
    label 'Hoofdleverancier'
,   lsi.purchaseprice
,   itm.costpricestandard
    label 'Kostprijs'
,   itm.CostPriceNew
,   sip.price
    label 'Verkoopprijs'
,   CASE
    WHEN sip.Price = 0.00
    THEN 0.0
    ELSE round(((sip.Price  - itm.CostPriceStandard) / sip.Price),2)
    END
    ratio_Margin
    label 'Verkoopmarge'
from ExactOnlineREST.Logistics.SupplierItems lsi
inner join ExactOnlineREST.Logistics.Items itm
on itm.code = lsi.itemcode
and itm.enddate is null 
inner join ExactOnlineREST.Logistics.SalesItemPrices sip
on sip.itemcode = lsi.itemcode
and sip.unit = lsi.purchaseunit
and sip.enddate is null 
and sip.account is null
where lsi.supplierdescription like "%Vinites%"
and lsi.mainsupplier = true;

Call stack:

InvantiveSystemException
ValidationException
   bij Invantive.Basics.ValidationException..ctor(GlobalState owner, ExecutionOptions executionOptions, String messageCode, String errorMessage, String kindRequest, String localStackTrace, String nk, Exception innerException, Boolean inheritMessageCodeWhenPresent, Nullable`1 uid, Boolean isRecoverable, String poolIdentityId, String url)
   bij Invantive.Basics.InvantiveSystemException..ctor(GlobalState owner, ExecutionOptions executionOptions, String validationCode, String errorMessage, String kindRequest, String stackTrace, String nk, Exception innerException, Boolean inheritMessageCodeWhenPresent, Nullable`1 uid, Boolean isRecoverable, String poolIdentityId)
   bij Invantive.Data.Providers.OData.ODataProvider.GetServerSideFilters(GlobalState owner, ExecutionOptions executionOptions, String dataObject, String requestedObject, ODataObjectDefinition objectDefinition, JsonDatabaseColumnDefinitionCollection fields, QueryObject queryObject, ParameterList parameters, String baseServiceUrl, Dictionary`2& clientSideFilters, String[]& serviceUrls)
   bij Invantive.Data.Providers.OData.ODataProvider.FetchYieldUnfilteredInternal(GlobalState owner, ExecutionOptions executionOptions, EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameters, Boolean fetchSingle, Boolean fetchCountFirst, Int32 pagingSteps, Boolean fetchCount)
   bij Invantive.Data.GenericProvider.FetchYieldUnfiltered(GlobalState owner, ExecutionOptions executionOptions, EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameters, Boolean fetchSingle, Boolean fetchCountFirst, Int32 pagingSteps, Boolean fetchCount)
   bij Invantive.Data.Providers.ExactOnline.ExactOnlineProvider.FetchYieldUnfilteredInternal(GlobalState owner, ExecutionOptions executionOptions, EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameters, Boolean fetchSingle, Boolean fetchCountFirst, Int32 pagingSteps, Boolean fetchCount)
   bij Invantive.Data.Providers.ExactOnline.ExactOnlineProvider.FetchYieldUnfiltered(GlobalState owner, ExecutionOptions executionOptions, EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameters, Boolean fetchSingle, Boolean fetchCountFirst, Int32 pagingSteps, Boolean fetchCount)
   bij Invantive.Data.ConnectionManager.ExecuteProviderFetchYield(GlobalState owner, ExecutionOptions executionOptions, EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameters, Boolean fetchSingle, Boolean fetchCountFirst, Int32 pagingSteps, Boolean fetchCount)
   bij Invantive.Data.ConnectionManager.FetchYieldUnfiltered(GlobalState owner, ExecutionOptions executionOptions, EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameters, Boolean fetchSingle, Boolean fetchCountFirst, Int32 pagingSteps, Boolean fetchCount)
   bij Invantive.Sql.V1.DataSourceOrFunctionTree.<GetData>d__51.MoveNext()
   bij Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   bij Invantive.Sql.V1.FirehoseResultSet.VU.MoveNext()
   bij Invantive.Sql.V1.FirehoseResultSet.RU.MoveNext()
   bij Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   bij Invantive.Sql.V1.FirehoseResultSet.VU.MoveNext()
   bij Invantive.Sql.V1.FirehoseResultSet.RU.MoveNext()
   bij Invantive.Data.ExtensionMethods.RZK`1.MZK.MoveNext()
   bij Invantive.Data.ExtensionMethods.ZZK`1.R()
   bij Invantive.Data.ExtensionMethods.ZZK`1.LZK.MoveNext()
   bij System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   bij System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   bij Invantive.Sql.V1.JoinIterator.<InnerLeftRightOuterJoin>d__18.MoveNext()
   bij Invantive.Sql.V1.JoinIterator.<JoinInternal>d__17.MoveNext()
   bij Invantive.Sql.V1.JoinIterator.<Iterator>d__29.MoveNext()
   bij Invantive.Sql.V1.FirehoseResultSet.RU.MoveNext()
   bij Invantive.Sql.V1.FilterIterator.ZU.MoveNext()
   bij Invantive.Sql.V1.FirehoseResultSet.RU.MoveNext()
   bij Invantive.Sql.V1.ChainedFirehose.FU.MoveNext()
   bij Invantive.Sql.V1.FirehoseResultSet.RU.MoveNext()
   bij Invantive.Sql.V1.SelectListIterator.OT.MoveNext()
   bij Invantive.Sql.V1.FirehoseResultSet.RU.MoveNext()
   bij Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   bij Invantive.Sql.V1.FirehoseResultSet.VU.MoveNext()
   bij Invantive.Sql.V1.MaterializeSparseArrayIterator.<Iterator>d__1.MoveNext()
   bij Invantive.Sql.V1.QueryPlan.ST.MoveNext()
   bij Invantive.Data.ExtensionMethods.RZK`1.MZK.MoveNext()
   bij System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   bij System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   bij Invantive.Data.ConnectionManager.KS.L(QueryPlan )
   bij System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
   bij System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   bij System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   bij Invantive.Data.ConnectionManager.ExecuteAndCombineQueryPlansV1(GlobalState owner, ExecutionOptions executionOptions, QueryPlan[] queryPlans, String sqlStatement, ParameterList parameters)
   bij Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   bij Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   bij Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters)
   bij Invantive.Producer.Windows.Forms.QueryToolResults.BBK(GlobalState , ExecutionOptions , IProgressNotifier , String , ParameterList , Boolean , Boolean , Boolean , Boolean )
   bij Invantive.Producer.Windows.Forms.QueryToolResults.WBK(GlobalState , ExecutionOptions , String , String , Boolean )
   bij Invantive.Producer.Windows.Forms.QueryToolResults.ExecuteStatements(GlobalState owner, ExecutionOptions executionOptions)
   bij Invantive.Producer.Windows.Forms.QueryToolDocumentViewModel.WZ.QZ.MoveNext()
   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.Runtime.CompilerServices.AsyncMethodBuilderCore.MoveNextRunner.Run()
   bij System.Threading.ThreadPoolWorkQueue.Dispatch()
   bij Invantive.Data.Providers.OData.ODataProvider.GetServerSideFilters(GlobalState owner, ExecutionOptions executionOptions, String dataObject, String requestedObject, ODataObjectDefinition objectDefinition, JsonDatabaseColumnDefinitionCollection fields, QueryObject queryObject, ParameterList parameters, String baseServiceUrl, Dictionary`2& clientSideFilters, String[]& serviceUrls)
   bij Invantive.Data.Providers.OData.ODataProvider.FetchYieldUnfilteredInternal(GlobalState owner, ExecutionOptions executionOptions, EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameters, Boolean fetchSingle, Boolean fetchCountFirst, Int32 pagingSteps, Boolean fetchCount)
   bij Invantive.Data.GenericProvider.FetchYieldUnfiltered(GlobalState owner, ExecutionOptions executionOptions, EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameters, Boolean fetchSingle, Boolean fetchCountFirst, Int32 pagingSteps, Boolean fetchCount)
   bij Invantive.Data.Providers.ExactOnline.ExactOnlineProvider.FetchYieldUnfilteredInternal(GlobalState owner, ExecutionOptions executionOptions, EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameters, Boolean fetchSingle, Boolean fetchCountFirst, Int32 pagingSteps, Boolean fetchCount)
   bij Invantive.Data.Providers.ExactOnline.ExactOnlineProvider.FetchYieldUnfiltered(GlobalState owner, ExecutionOptions executionOptions, EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameters, Boolean fetchSingle, Boolean fetchCountFirst, Int32 pagingSteps, Boolean fetchCount)
   bij Invantive.Data.ConnectionManager.ExecuteProviderFetchYield(GlobalState owner, ExecutionOptions executionOptions, EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameters, Boolean fetchSingle, Boolean fetchCountFirst, Int32 pagingSteps, Boolean fetchCount)
   bij Invantive.Data.ConnectionManager.FetchYieldUnfiltered(GlobalState owner, ExecutionOptions executionOptions, EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameters, Boolean fetchSingle, Boolean fetchCountFirst, Int32 pagingSteps, Boolean fetchCount)
   bij Invantive.Sql.V1.DataSourceOrFunctionTree.<GetData>d__51.MoveNext()
   bij Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   bij Invantive.Sql.V1.FirehoseResultSet.VU.MoveNext()
   bij Invantive.Sql.V1.FirehoseResultSet.RU.MoveNext()
   bij Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   bij Invantive.Sql.V1.FirehoseResultSet.VU.MoveNext()
   bij Invantive.Sql.V1.FirehoseResultSet.RU.MoveNext()
   bij Invantive.Data.ExtensionMethods.RZK`1.MZK.MoveNext()
   bij Invantive.Data.ExtensionMethods.ZZK`1.R()
   bij Invantive.Data.ExtensionMethods.ZZK`1.LZK.MoveNext()
   bij System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   bij System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   bij Invantive.Sql.V1.JoinIterator.<InnerLeftRightOuterJoin>d__18.MoveNext()
   bij Invantive.Sql.V1.JoinIterator.<JoinInternal>d__17.MoveNext()
   bij Invantive.Sql.V1.JoinIterator.<Iterator>d__29.MoveNext()
   bij Invantive.Sql.V1.FirehoseResultSet.RU.MoveNext()
   bij Invantive.Sql.V1.FilterIterator.ZU.MoveNext()
   bij Invantive.Sql.V1.FirehoseResultSet.RU.MoveNext()
   bij Invantive.Sql.V1.ChainedFirehose.FU.MoveNext()
   bij Invantive.Sql.V1.FirehoseResultSet.RU.MoveNext()
   bij Invantive.Sql.V1.SelectListIterator.OT.MoveNext()
   bij Invantive.Sql.V1.FirehoseResultSet.RU.MoveNext()
   bij Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   bij Invantive.Sql.V1.FirehoseResultSet.VU.MoveNext()
   bij Invantive.Sql.V1.MaterializeSparseArrayIterator.<Iterator>d__1.MoveNext()
   bij Invantive.Sql.V1.QueryPlan.ST.MoveNext()
   bij Invantive.Data.ExtensionMethods.RZK`1.MZK.MoveNext()
   bij System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   bij System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   bij Invantive.Data.ConnectionManager.KS.L(QueryPlan )
   bij System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
   bij System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   bij System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   bij Invantive.Data.ConnectionManager.ExecuteAndCombineQueryPlansV1(GlobalState owner, ExecutionOptions executionOptions, QueryPlan[] queryPlans, String sqlStatement, ParameterList parameters)
   bij Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
--- Einde van stacktracering vanaf vorige locatie waar uitzondering is opgetreden ---
   bij System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   bij Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   bij Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
--- Einde van stacktracering vanaf vorige locatie waar uitzondering is opgetreden ---
   bij System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   bij Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   bij Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters)
   bij Invantive.Producer.Windows.Forms.QueryToolResults.BBK(GlobalState , ExecutionOptions , IProgressNotifier , String , ParameterList , Boolean , Boolean , Boolean , Boolean ) in c:\smoke\ws-22.0\Invantive.Producer\src\Invantive.Producer.Windows.Forms\QueryTool\QueryToolTraceViewModel.cs:regel 17
   bij Invantive.Producer.Windows.Forms.QueryToolResults.WBK(GlobalState , ExecutionOptions , String , String , Boolean ) in c:\smoke\ws-22.0\Invantive.Producer\src\Invantive.Producer.Windows.Forms\QueryTool\QueryToolResultsViewModel.cs:regel 22

Hoe los ik dit op?

2

Het probleem is niet reproduceerbaar en hangt waarschijnlijk samen met de daadwerkelijke data.

Advies is om op Invantive Cloud een Exact Online-database aan te maken, het probleem hierop te reproduceren (voeg een ‘@eol’ toe aan het einde van elke tabelnaam) en dan delegatie te verlenen aan het e-mailadres dat via een apart kanaal medegedeeld zal worden.

3

Ik heb een vergelijkbare foutmelding op 22.0.480:

itgenoda462:
No URLs generated for filters on table ‘ExactOnlineREST.Project.PjtTimeTransactions’

create or replace table ptn@inmemorystorage
as
select ptn.*
,      to_char(ptn.date, 'YYYYMM')
       timeslot
from   pjttimetransactions@eol ptn
where  ptn.activity is not null
and    ptn.project in ( select id from pjt@inmemorystorage )
and    ptn.date between coalesce(to_date('', 'YYYYMMDD'), ptn.date) and coalesce(to_date('20221130', 'YYYYMMDD'), ptn.date)
and    ptn.hourstatus in (20 /* Final. */)

Melding:

2023-01-08 17:24:04.669 Information itgensql264: Invantive SQL statement started.
2023-01-08 17:24:04.841 Warning itgendhb173: Context: create or replace table ptn@inmemorystorage as select ptn.* , ...YYYMMDD'), ptn.date) and    ptn.hourstatus in (20 /* Final. */)
2023-01-08 17:24:04.857 Exclamation itgencun016: Exclamation itgenoda462: No URLs generated for filters on table 'ExactOnlineREST.Project.PjtTimeTransactions'.
2023-01-08 17:24:06.872 Error itgenoda462: InvantiveSystemException
   at Invantive.Basics.ValidationException..ctor(GlobalState owner, ExecutionOptions executionOptions, String messageCode, String errorMessage, String kindRequest, String localStackTrace, String nk, Exception innerException, Boolean inheritMessageCodeWhenPresent, Nullable`1 uid, Boolean isRecoverable, String poolIdentityId, String url)
   at Invantive.Basics.InvantiveSystemException..ctor(GlobalState owner, ExecutionOptions executionOptions, String validationCode, String errorMessage, String kindRequest, String stackTrace, String nk, Exception innerException, Boolean inheritMessageCodeWhenPresent, Nullable`1 uid, Boolean isRecoverable, String poolIdentityId)
   at Invantive.Data.Providers.OData.ODataProvider.GetServerSideFilters(GlobalState owner, ExecutionOptions executionOptions, String dataObject, String requestedObject, ODataObjectDefinition objectDefinition, JsonDatabaseColumnDefinitionCollection fields, QueryObject queryObject, ParameterList parameters, String baseServiceUrl, Dictionary`2& clientSideFilters, String[]& serviceUrls)
   at Invantive.Data.Providers.OData.ODataProvider.FetchYieldUnfilteredInternal(GlobalState owner, ExecutionOptions executionOptions, EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameters, Boolean fetchSingle, Boolean fetchCountFirst, Int32 pagingSteps, Boolean fetchCount)
   at Invantive.Data.GenericProvider.FetchYieldUnfiltered(GlobalState owner, ExecutionOptions executionOptions, EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameters, Boolean fetchSingle, Boolean fetchCountFirst, Int32 pagingSteps, Boolean fetchCount)
   at Invantive.Data.Providers.ExactOnline.ExactOnlineProvider.FetchYieldUnfilteredInternal(GlobalState owner, ExecutionOptions executionOptions, EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameters, Boolean fetchSingle, Boolean fetchCountFirst, Int32 pagingSteps, Boolean fetchCount)
4

Gelieve deze query nogmaals te proberen na ingebruikname van 22.0.488, te vinden op https://releasenotes.invantive.com.

De query kan ook geherformuleerd worden tot de volgende, waardoor hij circa 60x sneller is bij 2e en volgende uitvoering:

Select lsi.itemcode
       label 'Code'
,      lsi.itemdescription
       label 'Omschrijving'
,      lsi.purchaseunit
       label 'Inkoop Eenheid'
,      lsi.supplierdescription 
       label 'Hoofdleverancier'
,      lsi.purchaseprice
,      itm.costpricestandard
       label 'Kostprijs'
,      itm.CostPriceNew
,      sip.price
       label 'Verkoopprijs'
,      case
       when sip.Price = 0.00
       then 0.0
       else round(((sip.Price  - itm.CostPriceStandard) / sip.Price), 2)
       end
       ratio_Margin
       label 'Verkoopmarge'
from   ExactOnlineREST.Logistics.SupplierItems lsi
join   ItemsIncremental itm
on     itm.code = lsi.itemcode
and    itm.enddate is null 
join   SalesItemPricesIncremental sip
on     sip.item    = itm.id
and    sip.unit    = lsi.purchaseunit
and    sip.enddate is null 
and    sip.account is null
where  lsi.supplierdescription like '%Vinites%'
and    lsi.mainsupplier = true
5

Ja we kunnen de query weer uitvoeren zonder foutmelding. Hartelijk dank voor de geoptimaliseerde query!

1 like