| You can use the following SQL language statement types |
|---|
|
|
|
|
|
|
|
|
|
All statements can bind argument into the statements from Variables.
Also, you can make dynamic statement construction by using the Variable type: SQL raw. In effect dynamically constructing the SQL statement from text.
| DataETL support | Statement types |
|---|---|
| Extract From: | SELECT, WITH |
| Load Into: | All other supported statement types but SELECT, WITH |
| WITH keyword | Support for the SQL keyword WITH in conjunction with SELECT. This enables advanced result stet generation like a path hierarchy derived from a linked list of records. Useful for migration of folder structures from legacy Document Management Systems (DMS) like IBM FileNet.. This article describes more detail about selecting Parent-child relational rows. |
| SQLServer syntax |
with test as
(
select objectid, name, parentcontainerid, CAST('' AS VARCHAR(MAX)) as path from my_new_table where parentcontainerid = '' or parentcontainerid is null union all
select my_new_table.objectid, my_new_table.name, my_new_table.parentcontainerid,
CAST(CASE WHEN test.path = ''
THEN(CAST(test.name AS VARCHAR(MAX)))
ELSE(test.path + '\' + CAST(test.name AS VARCHAR(MAX)))
END AS VARCHAR(MAX)) as path
from my_new_table, test
where my_new_table.parentcontainerid = test.objectid)
select name, objectid, path, CASE WHEN path = '' THEN name ELSE path + '\' + name END as fullpath from test; |
| Firebird syntax |
with recursive test as
(
select objectid, name, parentcontainerid, CAST('' AS VARCHAR(2000)) as path from my_new_table where parentcontainerid = '' or parentcontainerid is null union all
select my_new_table.objectid, my_new_table.name, my_new_table.parentcontainerid,
CAST(CASE WHEN test.path = ''
THEN(CAST(test.name AS VARCHAR(2000)))
ELSE(test.path || '\' || CAST(test.name AS VARCHAR(2000)))
END AS VARCHAR(2000)) as path
from my_new_table, test
where my_new_table.parentcontainerid = test.objectid)
select name, objectid, path, CASE WHEN path = '' THEN name ELSE path || '\' || name END as fullpath from test; |
| SQL Server stored procedure support. | You can call stored procedures using the standard syntax like below. Note that you can pass in variables as arguments.Exec MyStoredProcedure([<My Variable argument>],'My other argument');Limitation: You cannot use Transact SQL stored procedure syntax directly in the script. You must create the procedure in the database and then call it. |
| SQL Server UNICODE support | INSERT/UPDATE SQL statements can use the SQLServer specific UNICODE syntax N'my string value' to support UNICODE character set for columns of type NVARCHAR. Notice tables columns created from the IDE will be of type VARCHAR. So, to support UNICODE you must create/alter your table using embedded SQL or use SQL Server Management Studio.  |
Last modified:
15 July 2022