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
1
vote
1 answer

itgencun017: After a fresh Invantive Data Hub install, my batch script isn't working anymore

My script wasn't executing correctly, so I took a look at the log file that's also created by the batch script. It shows the following error: 2017-09-05 07:34:24.324 Error itgencun016: Waarschuwing itgenuty427: Een verbinding met de database 'Oracle…
Richard
  • 21
  • 2
1
vote
1 answer

Consistent Id of XML output of SQL executed on Data Access Point

I run an application on Exact Oline for eco taxes, using XML output with XSL transformation to generate the HTML forms. A query like: select * from me generates:
H Jansen
  • 319
  • 1
  • 8
1
vote
1 answer

Automatically set logical view on Data Replicator table on Exact Online

I am using Invantive Data Hub with the Data Replicator option to replicate Exact Online in our Azure database. The tables with names such as: dcd_ganw2_wpbz0_djmwe are nicely created automatically and go away automatically. And column names like…
1
vote
1 answer

Improve performance of WareHouse query on Exact Online

We have 20 warehouses and 3.000 articles. Therefore there are 60.000 rows in the ItemWarehouses table of Exact Online. However, retrieval takes 1200 ms per 60 rows, so total query on this data volume for a warehouse analysis takes 3-4 hours. I've…
Joanna
  • 23
  • 2
1
vote
1 answer

How can this query be optimized for speed?

This query creates an export for UPS from the deliveries history: select 'key' , ACC.Name , CON.FullName , CON.Phone , ADR.AddressLine1 , ADR.AddressLine2 , ADR.AddressLine3 , ACC.Postcode , ADR.City , …
Leo H
  • 35
  • 5
1
vote
1 answer

Post message from SQL to a Slack channel using incoming webhook

I want to send messages based upon query on Exact Online to a Slack channel using the Slack incoming webhooks and Invantive SQL. How do I this without massive SQL functions to properly escape the JSON?
Guido Leenders
  • 4,232
  • 1
  • 23
  • 43
1
vote
1 answer

Insert Invoice into Exact Online

I want to insert an Invoice into Exact Online. When I insert the invoice manual, it works fine, but with the Invantive Control tool I get an error: insert into UploadXMLTopics ( topic , payload , division_code , orig_system_reference ,…
1
vote
1 answer

How to Include a reference to a table in Invantive 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…
1
vote
1 answer

Mass loading GL transactions into Exact Online

For performance testing reasons I want to load many General Ledger transactions into Exact Online. I am using the REST API for Transactions and their lines by posting transactions after authenticating using OAuth. However, this is quite slow and it…
Goombah
  • 2,835
  • 2
  • 12
  • 22
1
vote
1 answer

How to get available attributes of an Invantive SQL provider

There are several providers available for Invantive SQL. There are some shared provider attributes like apiUrl for Exact Online, but how can I without access to the source code determine the full list of configurable provider attributes available?
Guido Leenders
  • 4,232
  • 1
  • 23
  • 43
1
vote
1 answer

Select multiple Exact Online administrations based upon query

When you have many partitions (Exact Online administrations) like 2500, it can become quite cumbersome to select the right companies out of this list one-by-one and then copy & paste it into a large use COMPANY1, COMPANY2, COMPANY3, ...…
Guido Leenders
  • 4,232
  • 1
  • 23
  • 43
1
vote
1 answer

Combining multiple condition in single case statement

Does Invantive SQL support multiple condition in a single case statement? I the statement below, I did not get any results. Tried the same statement with only 1 condition (no cascade), this retrieved the expected result. select prj.code , …
1
vote
1 answer

Retrieve file name from path with Invantive SQL

For a conversion of AccountView XML input files to XML Auditfile Afrekensystemen (Cash Registers) I need to generate an output file name without a path. The query is: select f.file_path file_path_src , replace(f.file_path, '.xml', '.xaa')…
Goombah
  • 2,835
  • 2
  • 12
  • 22
1
vote
1 answer

Filling a Word template with data from Exact Online query returns itgendps155: Publication failed

When filling a Word template through SQL on Exact Online the following error occurs: Publication failed. Context: value-of…
H Jansen
  • 319
  • 1
  • 8
1
vote
2 answers

Choose different division of Exact Online when using distributed query with Invantive SQL

I have a set of SQL statements using distributed option of Invantive SQL that extract shipped goods information from Exact Online and create for each serial number shipped a ticket in Freshdesk, together with the consumer as a contact. This works…
H Jansen
  • 319
  • 1
  • 8