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 Add button in the top right.
   

   

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 Template button in the top right to display the query fields. If prompted, enter your JD Edwards log in credentials.
   

   

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 pressing the Add + button, typing the name, then pressing Save.

9. You can also select the Roles that are permitted to execute this service.

   

   Note

   Note this applies to Row security only (Type 4)
   Role security is not applied when queries, functions, reports are executed via the console.

10. Each query column can be modified to suit your specific requirements. Please see Defining a Query for futher details.
11. You can add filter fields to limit the number of records selected. Please see Adding a Filter for further details.
12. Click the Save button in the top right. 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, press the Duplicate Query button

Enter a name for the new query and press the Save button. Repeat this process for each additional definition you wish to add.

Defining a Query

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. Switch to the Query Filter page.
   

   

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 Trash can icon to the right of the line you wish to delete. The criteria is immediately removed.