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
2
votes
1 answer

How to include an Excel formula with a reference to a table in Invantive Control Excel add-in

I'm making a financial report in Excel using the data from Exact Online using the Invantive Control Excel Add-in. In Exact Online I have a classification for the General Ledgers. In my Excel file, I would like to match these classifications on my…
2
votes
1 answer

Empty columns when querying

When using the Invantive Query Tool to request the table GLTransactionlines on Exact Online, my query times out. When selecting a single column the query returns no data. Specifically, I would like to know from what table I can request my…
2
votes
1 answer

SQL select statement similar to vertical search approximate match in Excel

Having a table with columns 'price' and 'quantity'. e.g: rec price qty 1. 10,00 1 2. 7,50 5 3. 5,00 25 4. 3,00 100 I need to select the price for a quantity of 65. This is the price of record 3. Qty 65 is between qty 25…
H Jansen
  • 319
  • 1
  • 8
2
votes
1 answer

Create Exact Online companies en mass

To simulate an accountancy environment, I need to create at least 100 of Exact Online companies (divisions, administraties, dossiers). This can be achieved using a manual action, repeated 100 times. And repeated across 7 countries. In totaal 700…
Guido Leenders
  • 4,232
  • 1
  • 23
  • 43
2
votes
1 answer

Error: itgeneor003 Object metadata could not be retrieved for 'customers1' when creating table

When trying to execute the query below with the Exact Online Adapter in the Excel 2016 for Windows on Windows 10. We get the error Object metadata could not be retrieved for 'customers'. with error code itgeneor003. create or replace table…
2
votes
1 answer

Can not join Exact Online's Bill of Material Items and Items itself

When I run the following query to retrieve item details on items in the Bill of Materials (BOM) of Exact Online, I get no item details: select * from BillOfMaterialItemDetails bom join ExactOnlineREST..items itm on itm.ID =…
H Jansen
  • 319
  • 1
  • 8
2
votes
1 answer

How to add payment conditions to AR outstanding items in a report on Exact Online?

I am using Invantive Control to create an Excel report with some outstanding invoices information from Exact Online. I have created a model with the block designer and I have the outstanding invoices information I need. Now I also want to know the…
Jeroen
  • 21
  • 1
2
votes
1 answer

When syncing my model within Invantive Control for Excel I get the error "Could not find parameter with name 'P_SCHEME_CODE'

When I try to synchronise my model retrieving GL Account Classification information from Exact Online, I get the following error: itgensdf031: Could not find parameter with name 'P_SCHEME_CODE': What should I change in order for this to…
2
votes
1 answer

Notification: limited number of request exceeded during import of delivery tickets in Freshdesk

During import of goods deliveries from Exact Online as tickets in Freshdesk I got a message "You have exceeded the limit of request per hour". What is the maximum number of request allowed? Is there a workaround because the batch contains all…
H Jansen
  • 319
  • 1
  • 8
2
votes
2 answers

Connect Power BI with Exact Online more efficiently than through HTML

I am using Microsoft Power BI with Exact Online using Data Access Point on https://data-access-point.com using queries such as select * from exactonlinexml..aroutstandingitems. However, the URL must contain the user name and password to allow Data…
2
votes
1 answer

Validation failed error when inserting/updating Contact into Freshdesk using SQL

I want to update an existing Contact in Freshdesk via API using Invantive Control for Excel Cloud All. Invantive Control raises a validation error that email address and phone should be unique: Validation failed duplicate_value: email. It should be…
H Jansen
  • 319
  • 1
  • 8
2
votes
1 answer

Error invalid_value on Freshdesk when inserting contact persons from Exact Online

In a Freshdesk testing environment, the following SQL statement copies all contact persons involved with a sales order from Exact Online to Freshdesk: insert into contacts@freshdesk ( name , address , phone , email ) select delivery_account_name , …
2
votes
1 answer

SQL error in Invantive Control for Excel

This is for Invantive Control for Excel. What could be causing an error at the second CREATE TABLE? CREATE TABLE niveau1@inmemorystorage AS SELECT ID , Code , AddressLine1 , Name , Parent FROM Accounts WHERE …
Leo H
  • 35
  • 5
2
votes
1 answer

Exporting documents from Exact Online for one division gives a spreadsheet instead of separate files

Using the following SQL I get a list of documents and small thumbnails in Exact Online: select document_account_name || document_references_yourref || year(document_date) || '-' || month(document_date) || '-' || day(document_date) …
Joery
  • 23
  • 3
2
votes
0 answers

Intermittent error "Not authorized to access Exact Online" when accessing XML topic Settings of Exact Online

During querying on Exact Online, I sometimes get an intermittent error in the Invantive Query Tool: Not authorized to access Exact Online. Please check that you have access to the administration and have licensed a module for the data object…
Guido Leenders
  • 4,232
  • 1
  • 23
  • 43