Skip to content
Contact us Go to Customer Portal
  • There are no suggestions because the search field is empty.

Native Expression (Term)

Applies to
Platform: Microsoft SQL Server
Architecture: Stage

Native Expression (NativeExpression) replaces a source column with an expression that the source database evaluates in the OPENQUERY statement. Use it to convert or clean a value in the source system before it reaches Microsoft SQL Server.

The property exists only in the Microsoft SQL Server - Stage OpenQuery generator.

Where to set it

Set the property when you edit a term of a Source model object. It is a text field and empty by default.

Values

  • Empty (default): the native query reads the source column by its name.
  • An expression: the native query reads (<expression>) AS [<term name>] instead.

Write the expression in the SQL dialect of the source system, because the linked server passes it unchanged. Its result must have the data type of the term. Write single quotes once: the generator escapes them for each quoting level of the loader, which gives four quotes in the example.

Example

A Source CUSTOMER has Native Expression ISNULL(ADDRESS_LINE2, 'UNKNOWN') on the term ADDRESS_LINE2. The Stage loader contains this native query, here shortened:

FROM OPENQUERY([<linked server>], ''
SELECT [CUSTOMER_ID],[CUSTOMER],
  (ISNULL(ADDRESS_LINE2, ''''UNKNOWN'''')) AS [ADDRESS_LINE2]
FROM [<database>].[<schema>].[CUSTOMER]
'') AS [s1]

<linked server>, <database> and <schema> stand for the replaced placeholder values of the source system.

Related articles