Creating a Query - discovery - 23.1

Spectrum Discovery Guide

Product type
Software
Portfolio
Verify
Product family
Spectrum
Product
Spectrum > Discovery
Version
23.1
Language
English
Product name
Spectrum Discovery
Title
Spectrum Discovery Guide
Topic type
How Do I
Overview
Reference
First publish date
2007
ft:lastEdition
2024-02-07
ft:lastPublication
2024-02-07T17:21:58.768552
To create a new query in Spectrum Discovery, perform these steps:
  1. On the Discovery menu bar, click Query.
    The Query page is displayed.
  2. Click the Create Query button .
    The Create Query page is displayed.
  3. 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.

  4. 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.
  1. Create a query as you would normally do by using the Visual Query Builder.
  2. Locate the field or fields where you want the parameter to appear, and place your cursor in the Criteria field.
  3. 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.
  4. 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.
  5. On the Parameters workspace, do as follows:
    Note: The parameters you define here auto-updates in the corresponding SQL field.
    1. 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.
    2. Enter a name for your parameter.
    3. 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.
    4. Click Save to save the query.