-
On the Discovery menu bar, click Query.
The Query page is displayed.
-
Click the Create Query button .
The Create Query page is displayed.
-
On the Create Query page, define the fields as
follows:
Field Name
Description
Name
Enter a name for your query in the Name field.
Description
If you want to enter a description for your query as a reference for later use, enter it in the Description field.
Tags
Add tags to your query in the Tags field to give them a relevant context and make them easily accessible later. Tags are useful in categorizing the query—for example, Retail, Banking, Customer Data, and more.
The tags you add for your query appears on the corresponding query on the Query home page.
Model store connection
Select the type of Model Store connection you want to use to build the query.Note: You can only use the Model Store connections to build a query.To configure a new connection or edit any existing connections, click the Manage Connections link. It takes you to the Data Sources page in the Management Console. For details on configuring the Model Store connection, see Connecting to a Model Store.Note: Click the Refresh button to update any modification, deletion, or addition to your list of connections.SQL
It shows the queries you build using the Visual Query Builder.
Build SQL
Create a complex query by selecting multiple columns, creating joins, and nested queries by clicking Build SQL, which opens the Visual Query Builder. For more information, see the Visual Query Builder section of the Enterprise Data Integration Guide.
Preview
To see a sample of the records fetched by the SQL query, click Preview.
Parameters
It allows you to define parameters to act upon the resultset of the query dynamically. By using different parameters, a parameterized query returns different results. For more information, see Creating parameterized query.
- After you define the fields, click Save.
Creating parameterized query
- Create a query as you would normally do by using the Visual Query Builder.
- Locate the field or fields where you want the parameter to appear, and place your cursor in the Criteria field.
-
In the Criteria field, enter a question mark
?.
As you enter a question mark ?, you see the {field} value being added with a suffix 1,2,3 and so on to the WHERE clause in the corresponding SQL statement, as shown in the example below.
-
Click OK
You see the Create Query page with the Parameters workspace being auto-populated with the field or fields you chose to parameterize in Steps 2 and 3.
-
On the Parameters workspace, do as follows:
Note: The parameters you define here auto-updates in the corresponding SQL field.
-
Select an operator from any of these listed in the table
below.
Operation
Description
equals
Checks if the value in the field matches the value specified.
not equals
Checks if the value in the field does not match the value specified.
is greater than
Checks if the field has a numeric value that is greater than the value specified. This operator works on numeric data types as well as string fields that contain numbers.
is greater than or equals
Checks if the field has a numeric value that is greater than or equal to the value specified. This operator works on numeric data types as well as string fields that contain numbers.
less than
Checks if the field has a numeric value that is less than the value specified. This operator works on numeric data types as well as string fields that contain numbers.
is less than or equals
Checks if the field has a numeric value that is less than or equal to the value specified. This operator works on numeric data types as well as string fields that contain numbers.
is between
Selects values within a given range. The values can be numbers, text, or dates.
exists in
Selects multiple values, and specifies the list separated by new line. Only applicable for string or numeric fields.
like
Determines if a character string matches a specified pattern.
Note: The list of operators may vary based on the data type of your parameterized field. - Enter a name for your parameter.
-
Based on the data type of your parameterized field, you can enter or
select the data in the input field.
The input field box varies based on the data type. For example, if your parameterized field is of Date type, the input field would be a date-picker; if the parameterized field is a String type, the input field would be a text box.Note: It is mandatory to define valid values for the parameter; else, you will not be able to preview or save the query.
- Click Save to save the query.
-
Select an operator from any of these listed in the table
below.