All geek questions in one place

The same exact online query in Invantive Control for Excel gives different results when executed with a client

I have the following query to report revenue based on area groups from Exact Online:

select itemgroupcode 
,      itemgroupdescription 
,      code 
,      description 
,      country 
,      postcodegebied 
,      name 
,      financialyear 
,      financialperiod 
,      sum(quantity) 
       aantal 
,      sum(amountdc) 
       omzet  
,      sum(quantity2jaar) 
       aantal2014 
,      sum(omzet2jaar) 
       omzet2014 
,      sum(quantity1jaar) 
       aantal2015 
,      sum(omzet1jaar)
       omzet2015 
,      sum(quantityhuidigejaar) 
       aantal2016 
,      sum(omzethuidigejaar)
       omzet2016
from   ( select substr(act.postcode, 1, 2) 
         ,       case 
                 when financialyear = year(getdate()) - 0
                 then amountdc 
                 else 0 
                 end
                 * -1 
                 omzethuidigejaar 
         ,       case 
                 when financialyear = year(getdate()) - 1 
                 then amountdc 
                 else 0 
                 end
                 * -1 
                 omzet1jaar
         ,       case 
                 when financialyear = year(getdate()) - 2 
                 then amountdc 
                 else 0 
                 end
                 * -1  
                 omzet2jaar
         ,       case 
                 when financialyear = year(getdate()) - 0
                 then quantity 
                 else 0 
                 end
                 quantityhuidigejaar
         ,       case 
                 when financialyear = year (getdate()) - 1 
                 then quantity 
                 else 0 
                 end
                 quantity1jaar 
         ,       case 
                 when financialyear = year(getdate()) - 2 
                 then quantity 
                 else 0 
                 end
                 quantity2jaar 
         ,       case 
                 when substr(act.postcode, 1, 2) >= '10'
                      and substr(act.postcode, 1, 2) < '20' 
                 then '1000-1999'
                 when substr(act.postcode, 1, 2) >= '20' 
                      and substr(act.postcode, 1, 2) < '30' 
                 then '2000-2999' 
                 when substr(act.postcode, 1, 2) >= '30' 
                      and substr(act.postcode, 1, 2) < '40' 
                 then '3000-3999' 
                 when substr(act.postcode, 1, 2) >= '40' 
                      and substr(act.postcode, 1, 2) < '50'
                 then '4000-4999' 
                 when substr(act.postcode, 1, 2) >= '50' 
                      and substr(act.postcode, 1, 2) < '60'
                 then '5000-5999' 
                 when substr(act.postcode, 1, 2) >= '60'
                      and substr(act.postcode, 1, 2) < '70' 
                 then '6000-6999' 
                 when substr(act.postcode, 1, 2) >= '70'
                      and substr(act.postcode, 1, 2) < '80'
                 then '7000-7999' 
                 when substr(act.postcode, 1, 2) >= '80' 
                      and substr(act.postcode, 1, 2) <= '89'
                 then '8000-8999' 
                 when substr(act.postcode, 1, 2) >= '90' 
                      and substr(act.postcode, 1, 2) <= '99'
                 then '9000-9999'
                 else 'unknown' 
                 end
                 postcodegebied
         ,       -1 * tle.amountdc
         ,       tle.financialperiod
         ,       tle.financialyear
         ,       act.country
         ,       act.name
         ,       itm.code
         ,       itm.description
         ,       tle.quantity
         ,       itm.itemgroupdescription
         ,       itm.itemgroupcode
         from    transactionlines tle
         join    exactonlinerest.crm.accounts act 
         on      act.code = tle.accountcode
         join    exactonlinerest.financial.glaccounts glt 
         on      glt.code = tle.glaccountcode
         --
         -- Type 110: grootboekrekening van het type omzet
         --
         and     glt.type = 110 
         join    exactonlinerest.logistics.items itm
         on      tle.itemcode = itm.code 
         --
         -- zodat er alleen transacties worden meegenomen die op een artikel geboekt zijn.
         --
         where   tle.itemcode is not null
       ) tle2
group 
by     itemgroupcode
,      itemgroupdescription 
,      code 
,      description 
,      country 
,      postcodegebied
,      name 
,      financialyear 
,      financialperiod
order 
by     itemgroupcode 
,      financialyear 
,      itemgroupdescription 
,      code 
,      description 
,      country 
,      postcodegebied
,      name 
,      financialperiod

When executing this request on the client machine, I expect the same result. But the result is different. Each amount and quantity is multiplied by a factor of eight, depending on whether the query is being executed as an Exact Online user " ME@acme.com " or " OTHERPERSON@acme.com ".

How can this happen and how can I fix it?

+4
source share
1 answer

.

, -, , , () , join. , , , () . .

8 , 3- , , /, Exact Online, , (2, 3) = 8 .

, , , , 3 . financialyear >= ....

:

select itemgroupcode 
,      itemgroupdescription 
,      code 
,      description 
,      country 
,      postcodegebied 
,      name 
,      financialyear 
,      financialperiod 
,      sum(quantity) 
       aantal 
,      sum(amountdc) 
       omzet  
,      sum(quantity2jaar) 
       aantal2014 
,      sum(omzet2jaar) 
       omzet2014 
,      sum(quantity1jaar) 
       aantal2015 
,      sum(omzet1jaar)
       omzet2015 
,      sum(quantityhuidigejaar) 
       aantal2016 
,      sum(omzethuidigejaar)
       omzet2016
from   ( select substr(act.postcode, 1, 2) 
         ,       case 
                 when financialyear = year(getdate()) - 0
                 then amountdc 
                 else 0 
                 end
                 * -1 
                 omzethuidigejaar 
         ,       case 
                 when financialyear = year(getdate()) - 1 
                 then amountdc 
                 else 0 
                 end
                 * -1 
                 omzet1jaar
         ,       case 
                 when financialyear = year(getdate()) - 2 
                 then amountdc 
                 else 0 
                 end
                 * -1  
                 omzet2jaar
         ,       case 
                 when tle.financialyear = year(getdate()) - 0
                 then tle.quantity 
                 else 0 
                 end
                 quantityhuidigejaar
         ,       case 
                 when tle.financialyear = year (getdate()) - 1 
                 then tle.quantity 
                 else 0 
                 end
                 quantity1jaar 
         ,       case 
                 when tle.financialyear = year(getdate()) - 2 
                 then tle.quantity 
                 else 0 
                 end
                 quantity2jaar 
         ,       case 
                 when substr(act.postcode, 1, 2) >= '10'
                      and substr(act.postcode, 1, 2) < '20' 
                 then '1000-1999'
                 when substr(act.postcode, 1, 2) >= '20' 
                      and substr(act.postcode, 1, 2) < '30' 
                 then '2000-2999' 
                 when substr(act.postcode, 1, 2) >= '30' 
                      and substr(act.postcode, 1, 2) < '40' 
                 then '3000-3999' 
                 when substr(act.postcode, 1, 2) >= '40' 
                      and substr(act.postcode, 1, 2) < '50'
                 then '4000-4999' 
                 when substr(act.postcode, 1, 2) >= '50' 
                      and substr(act.postcode, 1, 2) < '60'
                 then '5000-5999' 
                 when substr(act.postcode, 1, 2) >= '60'
                      and substr(act.postcode, 1, 2) < '70' 
                 then '6000-6999' 
                 when substr(act.postcode, 1, 2) >= '70'
                      and substr(act.postcode, 1, 2) < '80'
                 then '7000-7999' 
                 when substr(act.postcode, 1, 2) >= '80' 
                      and substr(act.postcode, 1, 2) <= '89'
                 then '8000-8999' 
                 when substr(act.postcode, 1, 2) >= '90' 
                      and substr(act.postcode, 1, 2) <= '99'
                 then '9000-9999'
                 else 'unknown' 
                 end
                 postcodegebied
         ,       -1 * tle.amountdc
         ,       tle.financialperiod
         ,       tle.financialyear
         ,       act.country
         ,       act.name
         ,       itm.code
         ,       itm.description
         ,       tle.quantity
         ,       itm.itemgroupdescription
         ,       itm.itemgroupcode
         from    transactionlines tle
         join    exactonlinerest..accounts act 
         on      act.code = tle.accountcode
         and     act.division = tle.division
         join    exactonlinerest..glaccounts glt 
         on      glt.code = tle.glaccountcode
         --
         -- Type 110: GL Account of type Revenue.
         --
         and     glt.type     = 110 
         and     glt.division = tle.division
         join    exactonlinerest.logistics.items itm
         on      itm.code     = tle.itemcode
         and     itm.division = tle.division
         --
         -- Only transaction lines with an item.
         --
         where   tle.itemcode is not null
         --
         -- Only journal for revenues.
         --
         and     tle.journalcode = '70'
         --
         -- Optimization: not interested in older transactions than 2 years.
         --
         and     tle.financialyear >= year(getdate()) - 2
       ) tle2
group 
by     itemgroupcode
,      itemgroupdescription 
,      code 
,      description 
,      country 
,      postcodegebied
,      name 
,      financialyear 
,      financialperiod
order 
by     itemgroupcode 
,      financialyear 
,      itemgroupdescription 
,      code 
,      description 
,      country 
,      postcodegebied
,      name 
,      financialperiod
+1

Source: https://habr.com/ru/post/1664274/

More articles:


All Articles