v2023.07 ✅
The SQL statements and syntax used in the Extract SQL Statement panel must work with the SQL syntax supported by the ETL+ Source Driver used to connect to the associated Source Object.
Proper Usage of SQL in the Extract SQL Statement
Use T-SQL syntax for MS SQL Server sources.
For other source drivers refer to the source driver table below for the appropriate SQL variant. For instance, Use MySQL’s dialect of SQL for MySQL sources.
Use ANSI Standard SQL if unsure. In case of issues, be sure to check the source’s documentation and syntax since most of them have variations from the regular ANSI Standard. E.g.: see examples with Providex Joins at /wiki/spaces/TeckKB/pages/576094227.
SQL Syntax to Use with Various Source Drivers and Source Systems
Source Driver | SQL Syntax to Use |
|---|---|
Text File (.csv) | |
MS Excel | |
MS Access | |
MS SQL Server | Date and time data types and functions (Transact-SQL) Microsoft T-SQL – this site |
Sage 100 – ODBC Providex driver | Sage 100 PxPlus Reference Manual / Providex – Guide to finding SQL documentation in this reference manual.
|
Miscellaneous SQL Syntax Tips
CONVERT vs. CAST
SELECT
[column1],
1 AS [column2],
CONVERT(varchar(30), GETDATE() ) AS [column3]
FROM
[sourceTableName]
The CONVERT function is specific to T-SQL and is not part of the ANSI standard. Use CAST for non SQL Server data sources. ETL+ Extract SQL Statement: CONVERT()
Comments in SQL
-- 1 as [someTable] comments out 1 line /* comments out everything until closing delimiter more comments more comments */