Adding a SQL Query with ODBC connector without Sub-queries

When sub-queries are disabled on your source connection for ODBC, Nectari DataSync no longer parses your code with the settings and only executes raw code entered in the SQL Query window. Note that ERPs such as Sage 100 North America Standard use an older ODBC Driver, which does not support sub-queries, and can limit capability.

Note

For more information on Sage 100 North America Standard, here is the link to the Official documentation.

When adding an SQL Query (refer to Configuring Table Fields), there are multiple guidelines to be mindful of:

If delimiters are absolutely necessary with your ODBC Driver, you will have to set them.

To do a Join , your Tables must be placed inside {oj […]}.

Example

SELECT Customer.*
FROM {oj Customer INNER JOIN SalesReps ON Customer.SALESREP = SalesReps.SALESREP}

You cannot use the * (asterisk) without an alias in your SELECT statement if your SELECT statement joins more than one table. While this might not give you an error, there is a risk of introducing errors when you have multiple columns with the same name across the different tables .

Here is an example of a statement that would cause errors:
```
SELECT *
FROM {oj Customer INNER JOIN SalesReps ON Customer.SALESREP = SalesReps.SALESREP}
```
You cannot use multiple * (asterisk) with an alias in your SELECT statement. This issue also applies to multiple columns with the same name, although this will simply break when performing the Process Deleted Records task, as it will not be able to find the table from which the field comes.

Here is an example of a statement that would cause errors:
```
SELECT Customer.*, SalesReps.*
FROM {oj Customer INNER JOIN SalesReps ON Customer.SALESREP = SalesReps.SALESREP}
```
Avoid creating calculated fields in the SELECT statement. Use the menu options in the Field section instead (refer to Configuring Table Fields). In most cases, this will not cause a problem. However, when setting that field as a Primary Key or Tracking Key, there is a risk of introducing an error during the Incremental Load or Process Deleted Records task.

Important

If you wish to use functions, ODBC does not support T-SQL or PL/SQL functions. You need to use ODBC functions available to the driver. Please visit Microsoft’s documentation for more details regarding these functions.

Adding a Table not included in the Extraction zip file

Refer to Configuring Table Fields to use the following calculated field as Tracking Key for Incremental Load task if you wish to add a table.

Important

The table (or one of the tables if you have done a Join to get them from another table) must contain the DateUpdated and TimeUpdated fields.

{fn TIMESTAMPADD(SQL_TSI_SECOND,
{fn CONVERT( {fn FLOOR(  {fn CONVERT(TimeUpdated,SQL_FLOAT)} * 3600 ) } - {fn CONVERT( {fn FLOOR(  {fn CONVERT(TimeUpdated,SQL_FLOAT)} ) }, SQL_INTEGER) } *3600, SQL_INTEGER) },
{fn TIMESTAMPADD(SQL_TSI_HOUR, {fn CONVERT( {fn FLOOR(  {fn CONVERT(TimeUpdated,SQL_FLOAT)} ) }, SQL_INTEGER) } ,{fn CONVERT(DateUpdated,SQL_TIMESTAMP)} )}  )}