Question 209 - MCD - Level 1 discussion

Correct syntax to use where clause isWHERE city = :city AND state = :state

This question validates knowledge on using dynamic queries in DBselect operation.

Configure Dynamic Queries in the Select Operation

When you need to parameterize not only theWHEREclause but also parts of the query itself (for example, queries that compare tables that depend on a condition, or complex queries for which the project table columns need to vary), you can configure dynamic queries.

In the following example, you configure a dynamic query by using a full expression with a string in which the table depends on a variable$(vars.table). Although some of the query text is dynamic ('SELECT * FROM $(vars.table)), theWHEREclause still defines theWHEREcondition using input parameters: in this case,WHERE name = :name.

In your Studio flow, select theSelectoperation.

In the operation configuration screen, set theSQL Query Textfield toSELECT * FROM $(vars.table) WHERE name = :name.

Set theInput Parametersfield to{'name' : payload}.

The following screenshot shows the configuration in Studio:

Figure 3. Dynamic query configuration

In the XML editor, the<db:sql>configuration looks like this:

<set-variable variableName='table' value='PLANET'/>

<db:select config-ref='dbConfig'>

<db:sql>#['SELECT * FROM $(vars.table) WHERE name = :name']</db:sql>

<db:input-parameters>

#[{'name' : payload}]

</db:input-parameters>

</db:select>

You can apply input parameters only to parameters in aWHEREclause. To modify any other part of the query, use the DataWeave interpolation operator.

Mule Ref Doc:Query a Database Examples - Mule 4 | MuleSoft Documentation