////
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements.  See the NOTICE file
distributed with this work for additional information
regarding copyright ownership.  The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License.  You may obtain a copy of the License at
  http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied.  See the License for the
specific language governing permissions and limitations
under the License.
////
:documentationPath: /pipeline/transforms/
:language: en_US
:description: The Dynamic SQL Row transform allows you to execute a SQL statement that is defined in a database field. The lookup values returned by the transform are added as new fields onto the stream.

:openvar: ${
:closevar: }

= image:transforms/icons/dynamicsqlrow.svg[Dynamic SQL row transform Icon, role="image-doc-icon"] Dynamic SQL row

[%noheader,cols="3a,1a", role="table-no-borders" ]
|===
|
== Description

The Dynamic SQL Row transform allows you to execute a SQL statement that is defined in a database field.

The lookup values returned by the transform are added as new fields onto the stream.

|
== Supported Engines
[%noheader,cols="2,1a",frame=none, role="table-supported-engines"]
!===
!Hop Engine! image:check_mark.svg[Supported, 24]
!Spark! image:question_mark.svg[Maybe Supported, 24]
!Flink! image:question_mark.svg[Maybe Supported, 24]
!Dataflow! image:question_mark.svg[Maybe Supported, 24]
!===
|===

== Options

[options="header"]
|===
|Option|Description
|Transform name|Name of the transform; This name has to be unique in a single pipeline
|Connection|Select a database connection for the lookup
|SQL field name|Specify the field that contains the SQL to execute
|Number of rows to return|Specify the number of records to return. 0 means, return all rows
|Outer Join|- false: don't return rows where nothing is found - true: at least return one source row, the rest is NULL
|Replace variables|In case you want to use variables in the SQL, e.g. {openvar}table_name{closevar}, this option needs to be checked.
|Query only on parameters change|If your SQL statements do not change a lot, check this option to reduce the number of physical database queries.
|Template SQL|In Hop meta and data are separate so you have to specify the meta part in template SQL (field name and type).
I mean any statement that returns the same row structure.
|===