Skip to main content
Skip table of contents

Creating and Defining a Query

Query definitions you create are linked to tables or business views on the JD Edwards server. Once you create a query, you can load it to view and modify (where applicable) its data structure.

Note

Cantara adheres to the JD Edwards security model when using the XML List Kernel to access data. The content that is displayed for tables, views, and queries is based on your security level in JD Edwards.

Creating a Query

The procedure in this section shows you how to create a query.

To create a query service
  1. Select the Queries node in the navigation panel.
  2. Click the Create Query button. If prompted, enter your JD Edwards log in credentials.
  3. Enter the JDE Table/View.
  4. Select the type (Table or View) from the drop-down list. If this is incorrect the query will fail to load.
  5. Click the Load Table/View link to display the query fields.
  6. Enter a Name for the query that will be used to make the system calls. The value for this field can be a maximum of 40 characters (no spaces). Valid characters include numbers, letters, dash and underscore.
  7. You can choose to enter a Description for the function for future reference.
  8. You can add Tags to your function to assist with management of component defintions. You can select from the existing tags or add a new tag by entering it in the New Tag field and clicking the + button.
  9. Each query column can be modified to suit your specific requirements. Please see Defining a Query for futher details.
  10. You can add filter fields to limit the number of records selected. Please see Adding a Filter for further details.
  11. Click the Add Query button. The query definition is saved and is added to the list of queries in the navigation panel.


If you need to create multiple queries definitions for the same JD Edwards table or business view, you can change the name of the previous query you created and click the Save Query button. Repeat this process for each additional definition you want to add.

Defining a Query

Loading a table or business view displays all of the fields that are available to query.  You can then modify these fields as required.

Warning

Make sure you have saved a query before you attempt to define data selection criteria. The query must be saved as the specifications for each field are utilised to build the criteria and will be out of date if the fields are modified after the criteria have been defined.


The following table lists the parameters for a selected table or JD Edwards view.

FieldDescription
NameThe name you want to assign to the column. By default, the system populates this field with the JDE Name. You can change the value to one that is more meaningful to you. The value for this field can be a maximum of 40 characters (no spaces). Valid characters include numbers, letters, and a dash (-).
Table
The name of the table that the column is in.
AliasThe JD Edwards alias for the field.
Data Type

Data type for the parameter. The Cantara Access Server can determine the basic data types such as STRING, CHARACTER, and DATE, but is not able to automatically determine if the field is a complex data type such as ADDRESSNUMBER or BUSINESSUNIT unless it has been defined in the defaults definition file on the server. You can manually set these values to make sure the data sent to or returned from JD Edwards is handled correctly and to enable the advanced lookup functionality. Refer to the Definition Data Types table for details about each of the data types.

HiddenCheckbox to specify whether you want the table column to be displayed or hidden.
LookupIf selected, the system will execute the advanced lookup functionality for the data type specified. Refer to the Definition Data Types table for specific details about the lookup functionality of each of the data types.
SortSpecifies sort order: NONE, ASCENDING, DESCENDING.
SequenceSpecifies the ranking of the sort.

Adding a Filter

The Filter section of the query screen allows you to specify the rows you want the query to return.

To add a filter
  1. Go to the Filter section located at the bottom of the screen.
  2. Click the + Add Filter button
  3. Define the filter:
    • Condition - The criteria to apply on the filter: WHERE, AND, OR.
    • Column - The field to filter against.
    • Name - The name of the column as specified.
    • Operator - Equal (EQ), Not Equal (NE), Greater Than (GT), Less Than (LT), Less or Equal (LE), Greater or Equal (GE), In (IN), Not In (NI), Between (BW), and Not Between (NB).
    • Default Value - You can set default values for query criteria. For hidden fields only the default value will be submitted.

    • Hidden - Checkbox to specify whether you want the filter line item to be available to be submitted in the service call. Note: Click the area to activate the checkbox.
  4. If necessary, you can build the filter to contain multiple conditions. For example, after the first WHERE condition, you could use an AND or OR condition for more specific results.
  5. If you need to delete a specific filter line, click the Remove button for the line you want to delete. The criteria is immediately removed.

On This Page

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.