Learning
Community
Open Exchange
Global Masters
InterSystems IRIS Data Platform 2019.3 / System Interoperability / Business Process and Data Transformation Language Reference / DTL Elements / DTL <sql>
Previous section   Next section

DTL <sql>

Execute an embedded SQL SELECT statement within a data transformation.

Synopsis

<sql><![CDATA[   SELECT SSN INTO :context.SSN   FROM MyApp.PatientTable   WHERE PatID = :request.PatID ]]> </sql>

Elements

Element Purpose
<annotation> Optional. A text string that describes the <sql> element.

Description

The DTL <sql> element executes an arbitrary embedded SQL SELECT statement from within a DTL <transform> element.
To use the <sql> element effectively, keep the following tips in mind:
  • Always use the fully qualified name of the table, including both the SQL schema name and table name, as in:
    MyApp.PatientTable
    Where MyApp is the SQL schema name and PatientTable is the table name.
  • The contents of the <sql> element must contain a valid embedded SQL SELECT statement.
    It is convenient to place the SQL query within a CDATA block so that you do not have to worry about escaping special XML characters.
  • Any tables listed in the SQL query’s FROM clause must either be stored within the local InterSystems IRIS database or linked to an external relational database using the SQL Gateway.
  • Within the INTO and WHERE clauses of the SQL query, you can refer to a property of the source or target object by placing a : (colon) in front of the variable name. For example:
    <sql><![CDATA[
      SELECT Name INTO :target.Name
      FROM MainFrame.EmployeeRecord
      WHERE SSN = :source.SSN AND City = :source.Home.City
    ]]>
    </sql>
  • Only the first row returned by the query will be used. Make sure that your WHERE clause correctly specifies the desired row.
Previous section   Next section