Extract SQL Statement with UNION ALL Used in Load All, Replace, & Upsert

Example from a complex Extract SQL Statement with a UNION ALL clause from a X3 data source.

Load All

ETL+ extracts from the source table based on the Extract SQL Statement as is.

Extract SQL Statement panel for target table named SINVOICE

The query used to extract data from the data source tables is based on:

SELECT 
    AMTATI_0, ACCDAT_0, UPDDATTIM_0, ROWID, 1 as TblID
FROM 
  [SEED].[SINVOICE]
  UNION ALL
  SELECT 
    AMTATI_0, ACCDAT_0+1 AS ACCDAT_0, UPDDATTIM_0, ROWID, 2 as TblID
  FROM 
    [SEED].[SINVOICE]

For data sources that query the source with SQL will use the SQL above adapted as required for differences in SQL syntax.
For data sources what are not queried with SQL, ETL+ translates the SQL to the appropriate query language.

Load Replace or Load Upsert

Call outs. 1) columns listed in SELECT clause
2) FROM clause
3) Load Replace or Load Upsert criteria

Extract from Source to Staging Table

In this example ETL+ extracts from the source table based on SQL code derived from the Extract SQL Statement and the Replace or Upsert criteria.

ETL+ creates/sends to the source a new SELECT statement as following:
SELECT (<callout #1: SELECT columns>) 
FROM (<callout #2: FROM clause>)
WHERE (<callout #3: Replace/Upsert criteria)

Load from Staging Table to Target Table

SELECT (AMTATI_0, ACCDAT_0, UPDDATTIM_0, ROWID, TblID)
FROM 
      (
      SELECT AMTATI_0, ACCDAT_0, UPDDATTIM_0, ROWID, 1 as TblID 
      FROM [SEED].[SINVOICE]
      UNION ALL
      SELECT AMTATI_0, ACCDAT_0 AS ACCDAT_0, UPDDATTIM_0, ROWID, 2 as TblID
      FROM [SEED].[SINVOICE])
      )
WHERE (ACCDAT_0 >= '01/01/2023')