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

Query repository file contents from GitLab

I want retrieve the commit id of a file readmeTest.txt through Invantive SQL like so: select * from repository_files(29, file-path, 'master') But for this to work I need a project-id, file-path and a ref. I know my project-id (I got it from select…
0
votes
1 answer

itgenoda074 SysAccessDenied on retrieval of binary blob in DocumentAttachmentFiles

When I execute the following query on Exact Online: use 450*** /* Replace anonymized *** by last three digits of division. */ set use-http-disk-cache false set use-http-memory-cache false select * from ExactOnlineREST..DocumentAttachmentFiles…
user9461953
0
votes
1 answer

Invantive Query Tool error querying NMBRS.nl

I want to run a query in Nmbrs.nl getting the journal information of a run. So I selected a company by using: using 111111 Then the following query to get the run info: select * from CompanyRunsYear(2017) This gives me a list of the runs in…
RS Finance
  • 224
  • 1
  • 9
0
votes
1 answer

Distinguish Supplier from Customer with Exact Online results

I have the following query: select act.Division , act.DivisionName , act.code , act.name , act.status , acs.description , act.IsSupplier , act.IsSales from exactonlinerest..accounts act left outer join …
Goombah
  • 2,835
  • 2
  • 12
  • 22
0
votes
1 answer

itgenoda131 "The server is currently unavailable" on Exact Online with join in query

Running a query with a join with join_set on Exact Online Items, I receive an: The server is currently unavailable (because it is overloaded or down for maintenance). Generally, this is a temporary state.…
Guido Leenders
  • 4,232
  • 1
  • 23
  • 43
0
votes
1 answer

Is it possible to create a sales order in Exact Online using REST API of Invantive Control

I want to create a Sales Order in Exact Online in the table SalesOrders. This works using the REST API controlled by Invantive Control. However, Exact Online reports that the sales order lines are missing. How can I create the salesorder, since in…
H Jansen
  • 319
  • 1
  • 8
0
votes
1 answer

Project Report with internal rate from Exact Online in Invantive SQL

I would like to make a report in Invantive using the internal rate from Exact Online Projectmanagement. I am not able to find the right table for this report. I can't find the information which has the dates with corresponding internal rate per…
0
votes
1 answer

itgensdf054 on synchronize in Invantive Control

When synchronizing a model in Excel with one block, I receive an itgensdf054 error which indicates that it could not clear the target range where the data of the query below is synchronized into. The full error message is: Could not clear contents…
H Jansen
  • 319
  • 1
  • 8
0
votes
1 answer

How to insert a blank column in a SQL query?

This is the SQL code I currently use via the Invantive Control for Excel, linked with our Exact Online DB. As you can see in the code, in front of the line of the second select case I would like to enter 4 blank columns. The way it's coded right now…
David Geers
  • 3
  • 1
  • 3
0
votes
1 answer

String to double or decimal

How to convert a string to a double or decimal? In Exact Online (REST API) I try to calculate with a decimal value in a string field. e.g items.netprice + items.notes. The field items.notes contains the decimal value. Tried using cast and convert…
H Jansen
  • 319
  • 1
  • 8
0
votes
1 answer

Mass upload pictures for performance testing

To test performance with many and large binary objects, I need 1.000 or more document attachments to be available within Exact Online. Loading them through the user interface is quite cumbersome: many clicks per document and there is no easy drag &…
Guido Leenders
  • 4,232
  • 1
  • 23
  • 43
0
votes
1 answer

Exact Online query with joins runs more than 15 minutes

I am using the following query: set use-result-cache false set use-http-cache false create or replace table settings@inmemorystorage as select '29676ec4-61b5-45eb-a5a3-6feffe03d1d3' sor_id , '[[Exploded]]' exploded_signal_text , …
H Jansen
  • 319
  • 1
  • 8
0
votes
1 answer

Upload XML into Exact Online exceeding maximum size or response time

I have several XML files generated by a industry solution with new data to be uploaded into Exact Online using the XML API, either directly or using the UploadXmlTopics table in Invantive SQL with the Exact Online driver. However, the Exact Online…
Guido Leenders
  • 4,232
  • 1
  • 23
  • 43
0
votes
1 answer

Analyze data volume of API calls with Invantive SQL

The SQL engine hides away all nifty details on what API calls are being done. However, some cloud solutions have pricing per API call. For instance: select * from transactionlines retrieves all Exact Online transaction lines of the current…
Guido Leenders
  • 4,232
  • 1
  • 23
  • 43
0
votes
1 answer

Retrieve list of accounts with specific classification

I want to retrieve a list of customer names and a list of the assigned account manager and region as registered using a custom property. Using the following query, I get multiple lines per customer: select accountclassifications_account_name , …
Guido Leenders
  • 4,232
  • 1
  • 23
  • 43