Skip to main content

Query Editor Dialog Box

  • Updated

BackPrevious Topic  Next TopicNext

Query Editor Dialog Box

You can use the Query Editor dialog box to create or edit a query, or edit the data resources used for a business view in the same way. This topic describes the options in the dialog box.

Designer displays the Query Editor dialog box when you do one of the following:

  • In the Catalog Manager, right-click the Queries node, select New Query from the shortcut menu, type a name for the new query and select OK, then add the resources based on which to create the query in the Add Tables/Views/Queries dialog box and select OK.
  • In the Catalog Manager, right-click a query and select Edit Query on its shortcut menu.
  • In the Catalog Manager, right-click the Business Views node, select New Business Views from the shortcut menu, type a name in the displayed dialog box and select OK, then in the Add Tables/Views/Queries dialog box, add more than one resource to use in the business view and select OK.
  • Select Menu > Tools > Query Editor or select Query Editor in the Business View Editor dialog box.
  • In the Data panel, right-click a query and select Edit Query from the shortcut menu.
  • When creating or editing a query-based data component, select a query in the Data screen of the report wizard and select the Edit button.

Query Editor

You see the following options in the dialog box:

Menu

  • Query
    • Apply
      Select to apply the changes to the query.
    • Save As
      Select to save the query with the name you specify.
    • Add Tables/Views/Queries
      Select to open the Add Tables/Views/Queries dialog box to add tables, views, synonyms, queries, imported SQLs, stored procedures, and user-defined data sources in the current catalog data source to the query. When you select to add a query, imported SQL, stored procedure, or user-defined data source, Designer adds it as a single table with all of its columns.
    • Delete Table/View/Query
      Select to remove the specified table from the query.
    • Union
      Select to create and modify a union for the query.
    • Filter
      Select to open the Search Condition dialog box to specify more criteria to limit the amount of data.
    • Select Distinct
      Select to use the SELECT DISTINCT command instead of SELECT in the SQL statement of the query. Designer enable the option when the query contains only tables/views/synonyms from one JDBC connection, or tables from the same collection of a database in a MongoDB connection.
    • Ignore Predicate If Parameter Value Is Null
      If you select this option, when the query uses a parameter and the parameter value is null at runtime, Logi Report Engine removes this condition from the query's or business view's criterion.

      When a query applies a String-typed parameter and the report users leave the value of the parameter blank at runtime:

      • If you select "Ignore Predicate If Parameter Value Is Null" for the query, Logi Report Engine regards the value of the parameter as "NULL" and does not display the predicate in the WHERE clause.
      • If you do not select "Ignore Predicate If Parameter Value Is Null" for the query, Logi Report Engine treats the value of the parameter as an empty string (¡®¡¯).
    • Customized SQL Mode
      Select to use the customized SQL mode, in which Designer disables all the functions in the Query Editor dialog box and you can only edit the query by modifying the SQL statement in the SQL window. Designer enables this option when the query contains only tables/views/synonyms from one JDBC connection.
    • Arrange Tables/Views/Queries
      Select to organize the tables that you add into the query.
    • Show Mapping Names
      Select to display the full names of the columns in the criteria panel. This option does not affect the contents in the definition. Designer enables this option when the query contains only tables/views/synonyms from one JDBC connection. When it is disabled, Designer treats the option as selected.
    • Show Table Names
      Select to display the full names of the columns in the tables in the criteria panel.
    • Show Paths
      Select to show information of the pre-join paths among tables in the query, when you enable the Pre-join feature.
    • Join Columns
      Select to join the specified columns in tables together.
    • Auto Join
      Select to automatically join the tables that you add to the query based on the default criteria set in the Options dialog box (File > Options > Query Editor > Auto Join). You can also reset the criteria to join the tables by selecting/clearing the items on the Auto Join submenu.
      • Join on foreign keys
        Select to automatically join tables in a query through a reference from Table A to a primary key in Table B. For example, an order form in Table A shows information on purchases that are made by a customer with a primary key of CustomerID. The customer ID # refers to a record in Table B which lists a specific address, phone number, name, and so on for the customer. CustomerID in Table A is a foreign key because it links the customer's ordering information in Table A to the customer's information in Table B using the CustomerID field.
      • Join on primary keys with same names
        Select to automatically join tables in a query through a field or a combination of fields that uniquely and specifically identifies a record. For example, OrderID may be the primary key in a table for Orders and also for Payments.
      • Join on same name
        Select to automatically join tables in a query through a link between two columns of the same name in two different tables. This often creates many invalid joins such as Amount that appears in both tables.
    • Current Query Options
      Select to open the Query Options dialog box to change the Auto Join and other options for the query.
  • Column
    • New Computed Column
      Select to open the New Computed Column dialog box to create functions to use for the query. Designer enables this option when the query contains only tables/views/synonyms from one JDBC connection.
    • Add Formula Fields
      Select to open the Add Formula Fields dialog box to add formulas to the query.
    • Delete Column
      Select to delete the specified column in the criteria panel. To add the column back, find it in the table and select its check mark.
  • View
    Designer enables the options in the View menu when the query contains only tables/views/synonyms from one JDBC connection.
    • Edit SQL
      Select to show the SQL statement of the query.
    • Preview
      Select to open the Preview Option dialog box to preview the records of the query.
  • Help
    Select to view information about the dialog box.

Toolbar

  • Add Tables
    Select to open the Add Tables/Views/Queries dialog box to add tables, views, synonyms, queries, imported SQLs, stored procedures, and user-defined data sources in the current catalog data source to the query. When you select to add a query, imported SQL, stored procedure, or user-defined data source, Designer adds it as a single table with all of its columns.
  • Delete Table
    Select to remove the specified table from the query.
  • New Computed Column
    Select to open the New Computed Column dialog box to create functions to use for the query. Designer enables this option when the query contains only tables/views/synonyms from one JDBC connection.
  • Add Formula Fields
    Select to open the Add Formula Fields dialog box to add formulas to the query.
  • Delete Column
    Select to delete the specified column in the criteria panel. To add the column back, find it in the table and select its check mark.
  • Arrange
    Select to organize the tables that you add into the query.
  • Preview
    Select to open the Preview Option dialog box to preview the records of the query. Designer enables this option when the query contains only tables/views/synonyms from one JDBC connection.
  • Help
    Select to view information about the dialog box.

Criteria panel

The panel lists all the columns that you select to use in the query. You can specifically define criteria for the columns in this panel.

SQL

Select to view the SQL statement of the query. Designer enables the button when the query contains only tables/views/synonyms from one JDBC connection.

Show Paths

Select to view information of the pre-join paths among tables in the query, when you enable the Pre-join feature.

OK

Select to apply all changes and close the dialog box.

Cancel

Select to close the dialog box without saving any changes.

Apply

Select to apply all changes and leave the dialog box open.

Help

Select to view information about the dialog box.

BackPrevious Topic  Next TopicNext