Previous section   Next section

DTL <sql>

Execute an embedded SQL SELECT statement within a data transformation.


      SELECT SSN INTO :context.SSN
      FROM MyApp.PatientTable
      WHERE PatID = :request.PatID ]]>


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


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:
    is the SQL schema name and
    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 Ensemble 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:
      SELECT Name INTO :target.Name
      FROM MainFrame.EmployeeRecord
      WHERE SSN = :source.SSN AND City = :source.Home.City
  • 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