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.

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.