Creating a Query

To create a new query in Spectrum Discovery, perform these steps:

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 a Query.

After you define the fields, click Save.

The query is displayed on the Query home page.

Creating parameterized query

A parameterized query allows you to create a query that can be updated easily to search upon a new term. To create a parameterized query, perform the following steps.

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.