OPT_WHERE

OPT_WHERE is a load option that will add a WHERE clause into generated DML at the lowest subquery level from a selected source table. This enables adding filters to automatically generated loads without needing to use OVERRIDE load steps and manually written SQL.

See also:

Load options

Usage

OPTION

OPT_WHERE: sql_code

Notes

OPT_WHERE can be used in loads that have a single entity mapping (i.e. one source entity) and no OVERRIDE load steps. OPT_WHERE will not have an effect if the load is overridden with an OVERRIDE load step.

For Google BigQuery Multi-Active Satellite this option requires usage of table alias as the hash list function is implemented with a join. Verify with the SQL Export that the parameter refers to the correct source table alias.

Examples

Filtering a hub load with OPT_WHERE

OPT_WHERE set to filter cases where source attribute VENDORID equals 1:

OPTION

OPT_WHERE: VENDORID = 1

Resulting DML generated by Agile Data Engine:

SQL

INSERT INTO
  dv.H_TRIP (
    ...
  )
SELECT
  DISTINCT ...
FROM
  (
    SELECT
      ...
    FROM
      stage.STG_TRIPDATA_YELLOW src_entity
    WHERE
      VENDORID = 1
  ) src
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      dv.H_TRIP trg
    WHERE
      trg.dv_id = src.dv_id
  );