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

Budget and actual rows not merged on full outer join

The following query: select coalesce(to_number(bl.division_code), bud.division) division , coalesce(bud.glaccountcode, bl.costcenter_costanalysis_period_periods_year_years_balance_code_attr) glaccountcode , coalesce(bud.costcenter,…
H Jansen
  • 319
  • 1
  • 8
1
vote
1 answer

"s:Client: Not Allowed List" error when running Loket.nl query

For one customer site, the following query: select /*+ ods(false) */ * from Werkgevers@loket wgr join PersonenPerWerkgever(wgr.entryid)@loket psn join WerknemerVerloningsrunsPerPersoon(wgr.entryid)@loket wvn returns an error with message code…
1
vote
1 answer

Download the Data Replicator feed messages raises itgencsr003 error

I execute the following statement to download the messages on the Data Replicator feed: alter persistent cache download feed This returns the following error: alter persistent cache download feed Error itgencsr003: Could not determine Data…
Goombah
  • 2,835
  • 2
  • 12
  • 22
1
vote
1 answer

How to convert text to numbers with Invantive SQL?

I am using the Invantive Query Tool. What is the right way to convert text to numbers? I get a text field from my database, constructed in a format like 3,4. When I use a calculation function in my query for example 2 * 3,4 it shows 68. What is the…
1
vote
1 answer

The attachments is empty in mailmessageattachments

When I execute the query as shown the attachment is always empty. I would expect that the contents of the attachment with name AttachmentFileName would be in the column Attachment. How do I retrieve the blob of the file? select /*+ join_set(mat,…
user9461953
1
vote
1 answer

Retrieving inactive employees

I have the following query using the Invantive Query Tool connecting to NMBRS. select e.number , es.EmployeeId , e.displayname , es.ParttimePercentage , es.startdate from Nmbrs.Employees.EmployeeSchedules es left outer…
RS Finance
  • 224
  • 1
  • 9
1
vote
1 answer

itgendch033: Backing databases require Invantive Data Replicator to restrict the number of columns to 1,000 for 'ExactOnlineXML.XML.SubscriptionLines'

When executing the batch script to replicate my data from Exact Online, I get the following error: Error itgencun016: Exclamation itgendch033: Backing databases require Invantive Data Replicator to restrict the number of columns to 1,000 for…
1
vote
1 answer

Retrieve Assortments (Dutch: "Assortimenten") on Exact Online

For a CSV dump of articles with stock and price information from Exact Online, I need to restrict the list of articles the CSV to articles in an Assortment (Dutch: "Assortiment"). The REST-APIs do not seem to offer this information. It is possible…
Goombah
  • 2,835
  • 2
  • 12
  • 22
1
vote
1 answer

How to get debtors from numbers for top 3 partitions

I want to get debtors from numbers from TOP 3 partitions. I am using this query to select partitions: use select top 1 code, data_container_alias from systempartitions@datadictionary where data_container_alias = 'nmbrs' And this query to get…
Andrius V.
  • 134
  • 1
  • 8
1
vote
1 answer

Execute Invantive SQL on Exact Online from Visual Basic for Applications (VBA)

I want to run some Invantive SQL statements from within VBA code on Excel on an Exact Online data source. I can use a UDF function call like: I_SQL_SELECT_SCALAR("select fullname from me") in an Excel sheet. How can I retrieve the full name from…
Guido Leenders
  • 4,232
  • 1
  • 23
  • 43
1
vote
0 answers

Get employee ID from NMBRS API

I have to query employee salaries information for statistical analysis. But using invantive-control and invantive query tool all EmployeeId fields are empty. And I need this field to add additional information. My question is how can I get…
Andrius V.
  • 134
  • 1
  • 8
1
vote
1 answer

Connect to SQL Server and Nmbrs in one session

I would like to insert data into SQL Server directly from NMBRS in a distributed transaction, like for instance: insert into table@sqlserver ( fields ) select fields from employees@nmbrs By default only the datadictionary, inmemorystorage, os and…
Andrius V.
  • 134
  • 1
  • 8
1
vote
1 answer

Restrict list of employees in NMBRS to just a few companies

I am creating a report on sick leave on nmbrs.nl using Invantive SQL. By default this query retrieves data across all companies: select * from employees emp join employeeabsence(emp.id) This takes an enormous amount of time since for each…
Andrius V.
  • 134
  • 1
  • 8
1
vote
1 answer

Tune slow grouping functions Invantive SQL

I have a database on PostgreSQL with some very large tables (with events and event measurements). For a date range on the measurement and one event type I want to compute the average duration with Invantive SQL. events (simplified, approximately 5…
Ronald Haan
  • 608
  • 4
  • 18
1
vote
1 answer

Sending mail via Office 365 smtp using Invantive SQL

I'm trying to send a e-mail using the Invantive Data Hub using the following query (password and username are redacted). insert into smtp@Mail ( fromEmail , toEmail , subject , body , smtpHostAddress , smtpUsername , smtpPassword ) VALUES (…
RS Finance
  • 224
  • 1
  • 9