Questions tagged [invantive-sql]

Invantive SQL is a SQL language and execution engine which retrieves and stores data in many traditional databases as well as cloud-based applications using a consistent SQL dialect, such as Exact Online, Freshdesk, Microsoft Dynamics, Stack Exchange, etc.​ Use this tag for questions about the use of Invantive SQL and make sure to add the appropriate tag for the system you are querying, like exact-online. For product use questions use Super User.

Invantive SQL is a SQL language and execution engine which retrieves and stores data in many traditional databases as well as cloud-based applications using a consistent SQL dialect, such as Exact Online, Freshdesk, Microsoft Dynamics, Stack Exchange, etc.

Invantive PSQL is a procedural extension on top of SQL included with Invantive SQL. Please use the tag Invantive SQL for PSQL questions.

Questions should include relevant SQL statements to reproduce the problem, when possible on the reference database Dummy. Also include appropriate tag(s) for the databases and/or cloud-applications you are querying, like or .

For product use questions use Super User.


If you want to ask a question about an error you get when firing a SQL statement, copy this template into your question:

Problem statement:

[explain what you want to achieve]

Steps to reproduce:

1. The first step

2. The second step

        The SQL code you used (don't use images but code formatting)

Expected result: [show or explain the expected result, preferably in text]

Actual result: [show or explain the actual result, preferably in text. If you received an error, include the full error in the block below]

> [The error message]
81 questions
3
votes
1 answer

Handcrafted OData queries on Exact Online with Invantive

We are currently running a number of hand-crafted and optimized OData queries on Exact Online using Python. This runs on several thousand of divisions. However, I want to migrate them to Invantive SQL for ease of maintenance. But some of the…
user9461953
3
votes
1 answer

Determine applicable division of Exactonlinerest..MailMessagesReceived

The mailmessagesreceived and mailmessagessent of Exact Online contain all work for all divisions of a customer. To retrieve them in our SQL Server database we use the following query: use select min(code) from systemdivisions group by…
3
votes
1 answer

How do I download only my purchase invoice documents from Exact Online with Invantive Query Tool?

To comply to regulations, I'm trying to download the purchase invoice documents (as PDF files) from some of my divisions to save them on-disk for archiving purposes. I use Invantive Query Tool to do this. I like to know which table to use and how to…
Berky
  • 31
  • 1
3
votes
1 answer

Register an AWS GIT code push event in Exact Online ERP

When a developer checks in code through a GIT push to AWS codecommit, I want to register an event in our ERP package Exact Online. This allows a project manager to review from within the ERP package the commits. AWS Codecommit only supports triggers…
Guido Leenders
  • 4,232
  • 1
  • 23
  • 43
3
votes
1 answer

Invantive Data Hub query on Exact Online returns too many rows

when I use Invantive Data Hub to dowload data from multiple Exact Online companies , I get duplicate rows when I expect one row per company. I use the following query: select gla.code , gla.description , gla.division , …
Mart
  • 31
  • 4
3
votes
1 answer

Error itgensql005: unknown identifier 'ID'

I'm using the Invantive Query Tool to update address data for suppliers in Exact Online on a large scale. I've written some VBA code that generates SQL code to put into the Invantive Query Tool. I've used this before and it worked fine, I've updated…
3
votes
1 answer

How optimize the performance of SerialNumbers by SalesInvoicesExploded?

I have the following query: select sie.invoicedate sie_invoicedate , sie.Silitem sle_item , sie.Silitemcode sle_itemcode , sie.Silitemdescription sle_itemdescription , sie.Silnetprice …
3
votes
2 answers

Error itgensql005 when fetching serial number from Exact Online GoodsDeliveryLines for upload to Freshdesk ticket

I want to exchange information between ExactOnline and Freshdesk based on deliveries (Exact Online Accounts -> Freshdesk Contacts, Exact Online deliveries -> Freshdesk tickets). The serial number of delivered goods is not available in either the…
2
votes
1 answer

When synchronizing my report with Teamleader I get a itgensql001 error for a custom field

When synching my Teamleader report in Invantive Control I get the following error: Unknown identifier 'cpyd.c_5_contactpersoon'. Screenshot of error Part of the query: select pjtd.pjt_title pjt_title , cpyd.c_5_contactpersoon…
Mieke D
  • 21
  • 2
2
votes
1 answer

Get list of blocked Exact Online divisions

We have a few thousand companies in Exact Online from which a certain percentage runs their own accounting and has their own license. However, there is a daily changing group of companies that are behind with their payments to Exact and therefore…
user9461953
2
votes
1 answer

'Use select' notation to specify multiple partitions of Exact Online data container

I'd like to select an active partition in the Data Hub. Normally I would use the following statement: INVANTIVE> use 1552839 2> Exclamation itgendhb077: Error in Invantive Data Hub. Database '1552839' does not exist. Make sure that the name is…
RS Finance
  • 224
  • 1
  • 9
2
votes
1 answer

Determine list of selected partitions on Exact Online

When I set the list of active partitions in Data Hub using: select code from systemdivisions where city='Rotterdam' to all customers in Rotterdam, the next queries will retrieve data across all customers. In Invantive Control I can see in the…
RS Finance
  • 224
  • 1
  • 9
2
votes
1 answer

Upload contents of CSV as new maximum stock position in Exact Online

I want to upload the contents of a CSV file as new values in Exact Online data set using for instance the following SQL statement: update exactonlinerest..ItemWarehouses set maximumstock=0 where id='06071a98-7c74-4c26-9dbe-1d422f533246' and …
2
votes
1 answer

How to extract number from a string

I've a string like 'intercompany creditors {DEMO[[1]]}'. I want to extract only the numbers from the string, in example just '1'. How to do this in Invantive SQL?
H Jansen
  • 319
  • 1
  • 8
2
votes
1 answer

Unkown Index error by using Invantive Control: "The index position 275 is not in use. The last index used is 7"

The error is on the column 'whs.description'. It won't give any error at the join 'ExactOnlineREST.Inventory.Warehouses Whs on Whs.ID=SHO.Warehouse' select Pro.code , ShopOrderNumber , SHO.status , SHO.Description , …
1
2 3 4 5 6