3

I have the following query:

select  sie.invoicedate         sie_invoicedate
,       sie.Silitem             sle_item
,       sie.Silitemcode         sle_itemcode
,       sie.Silitemdescription  sle_itemdescription
,       sie.Silnetprice         sle_netprice
,       sie.Silquantity         sle_quantity
,       sie.Silunitprice        sle_unitprice
,       ctr.ctr_code            ctr_code
,       ctr.ctr_name            ctr_name
,       ctr.parent_code         parent_code
,       ctr.parent_name         parent_name
,       gdlsn.ssrserialnumber   serialnumber
from    SalesInvoicesExploded sie
join    customers@inmemorystorage ctr
on      ctr.ctr_id = sie.invoiceto
join    GoodsDeliveryLineSerialNumbers gdlsn
on      gdlsn.salesorderlineid = sie.silid
where   sie.invoicedate >= '2016-01-01'
and     sie.invoicedate < '2016-01-03'
order
by      sie.invoicedate

How can I get the serial numbers only from the date range? In the debugger I see a lot of requests to Exact Online.

Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325
Frank Leemkuil
  • 83
  • 3

1 Answers1

1

For now, there isn't a very good filter possibility to get the result you want.

The problem is that there is no way to perform the gdlsn.salesorderlineid = sie.silid filter on the data set unless the data sets have been fetched from the other side.

Only specific filters are executed server-side (like your invoicedate >= '2016-01-01'). This is quite a hard nut to crack from the program side.

It would work if you can specify a filter that can be determined on beforehand, like that the date in GoodsDeliveryLineSerialNumbers.Created always comes after the invoicedate. It would mean a significant performance improvement if you can narrow down the set based on that date.

I suggest to use something like this, if possible:

select  sie.invoicedate         sie_invoicedate
,       sie.Silitem             sle_item
,       sie.Silitemcode         sle_itemcode
,       sie.Silitemdescription  sle_itemdescription
,       sie.Silnetprice         sle_netprice
,       sie.Silquantity         sle_quantity
,       sie.Silunitprice        sle_unitprice
,       ctr.ctr_code            ctr_code
,       ctr.ctr_name            ctr_name
,       ctr.parent_code         parent_code
,       ctr.parent_name         parent_name
,       gdlsn.ssrserialnumber   serialnumber
from    SalesInvoicesExploded     sie
join    customers@inmemorystorage ctr
on      ctr.ctr_id = sie.invoiceto
join    GoodsDeliveryLineSerialNumbers gdlsn
on      gdlsn.salesorderlineid = sie.silid
where   sie.invoicedate >= '2016-01-01'
and     sie.invoicedate < '2016-01-03'
-- add the following line, use a date that for sure will yield the rows:
and     gdlsn.created >= '2015-12-01'
--
order
by      sie.invoicedate
Goombah
  • 2,835
  • 2
  • 12
  • 22
Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325
  • I have executed the query but no results, all GoodsDeliveryLineSerialNumbers are fetched but no match. Is the key silid correct? – Frank Leemkuil Nov 14 '16 at 13:29
  • I guess the arbitrary filter `gdlsn.created >= '2016-01-01'` didn't yield any results. I can't say for sure that will yield the expected results as stated above. Is there a date you know for sure you can add as filter? – Patrick Hofman Nov 14 '16 at 13:31
  • The query still gives no results. I have changed the date range to: where sie.invoicedate >= '2016-01-01' and sie.invoicedate < '2016-02-01' -- add the following line: and gdlsn.created >= '2016-01-01' – Frank Leemkuil Nov 14 '16 at 16:10
  • What is outcomes of select count(*) of SalesInvoicesExploded and GoodsDeliveryLineSerialNumbers? – Guido Leenders Nov 14 '16 at 16:28
  • I think there were just no good delivery lines created in that period matching the sales invoices. Can you test how it works when you lower the date? – Patrick Hofman Nov 14 '16 at 16:40
  • We have plenty dilvery lines, every sales item has a corresponding delivery. If I run the query seperate I got results. Is the matching key correct? – Frank Leemkuil Nov 15 '16 at 07:59
  • I think we have to consider another solution, since this one doesn't seem feasible. I think we should try to optimize the SQL engine to enable passing filters along tables in the `join`. Let me check that and come back to you. – Patrick Hofman Nov 15 '16 at 08:01
  • Hi @Frank. I have been working on a concept of optimizing the performance in your specific case. It will take a little longer before it can be pushed to production, so hang in. – Patrick Hofman Nov 18 '16 at 12:38