Sort and Criteria Pane

Sort and Criteria Pane

Sort and Criteria Pane.

Use the Sort and Criteria Pane to Customise your Query Output.  In many circumstances you will not want your query to retrieve every row of data from the selected table in the database.  The UTB provides a number of criteria to enable selective retrieval of the data.

Define your data sorts and data selection criteria you wish to apply to a column.

Valid syntax for criteria is =, <, >, <>, Like, Not like, and Between

Other characters that will be of use are % and _

Column

Each of the Table rows selected in the Query Builder Pane is listed.  The database object properties name is displayed in full but will depend on your PC setup.  This name will appear in the Results Pane when the query is executed.

If you need to know the properties of any row in the table then this is available in the Data Source Tree Pane by expanding the columns in the required table and right clicking on the required row to display the ‘Properties’ selection option.  Click on this option to show the field Database Object Properties window.  In the example shown for the SDDOCO_Order_number field; it is 10 numeric characters with no decimal (0 Scale) and 4 bytes long.  This object properties information is not available in the Query Builder Pane.

Selected Columns appear in the list and may be deleted by highlighting and delete.

On highlighting a column a drop down arrow appears and on selection shows all the available columns in the table again.  By this means columns may be added to the Query but NOTE the new selected column REPLACES the highlighted column.

Alias

The Alias column is blank on initial selection but may be used to rename the Results Column in your query.

In the example shown the first three rows have been renamed from SDDCTO_Order_Type to Order Type, SDDOCO_Order_Number to Order No. and SDLNID_Line_Number to Line No.  (See the effect of this in the Results discussion below).

Tables

The Tables Column in the Sort and Criteria Pane shows the Table from which the Row was selected.

In the example shown the Rows SDDOCO_Order_Number is from the F4211_Sales_Order_Detail_File and SHVR01_Customer_PO (and two following rows) is from the F4201_Sales_Order_Header_File.

Output

On selection each output row is selected (Checked) and will appear in the Results Pane. If a table row is required for any form of Sort or selection Criteria then it must be selected in the Query Build Pane and appear here, but if it is NOT required in the Results Pane then it can be unchecked here.

In the example shown the SDRSDJ_Promised_Delivery and SDPA8_Parent_Number are both unchecked and will not appear in the Results Pane.

Sort Type and Sort Order

The output of your query can be sorted by using the Sort Type and Sort Order columns, which are related to each other. 

The Sort Type column has three options to specify the sort sequence of the rows in your query, Ascending, Descending or Unsorted.  On selection of either ascending or descending in the Sort Type field, the level of sort is entered in the Sort Order field.  If subsequently the unsorted option is selected then both Sort Type and Sort Order are cleared.

The Sort Order column allows you to specify on which field you wish to sort the data.  The number that you specify within the Sort Order column represents the order in which the query fields are sorted.  The field must be numeric and numbers sequential.

If the Sort Order is selected first then the Sort Type defaults to ascending, but it may be changed to descending.

In the example shown the query will be sorted by Order Type first in ascending type order, followed by Order Number in ascending order and followed by Order lines again in ascending order.  I.e. Order lines within order numbers within order types.

Criteria

The Criteria columns in the Sort and Criteria Pane are used to apply selection criteria to individual fields, thus allowing greater control of the rows that appear in the Results Pane of your query.

The following criteria are typical:

It should be noted that if the = criteria is required then it is automatically entered in the criteria column on tabbing out of the column.  It is NOT NECESSARY to enter it manually.

The format required for date criteria entry is important in the Sort and Select Pane.  The format is 'ccyy-mm-dd' i.e. '2006-04-28' and will appear correctly in the SQL statement.   Note: a '/' or '-' separator works).

In the example shown the query will select records where the Order Type is like Sx (where x is any character) and Order numbers are in the range between 2000 and 2650 and the branch plant is like anything with M30 in the last part of the field.

Other Selection Criteria in SQL and QBE

Other DATE Function Statements.

Note 1: The date functionality described below is fully applicable to sql Server database applications but NOT YET fully applied to jdeDirect applications.

Note 2: JdeDirect versions 2.0.4.5 and later return a ‘blank’ date in the database as a default of ‘31/12/1899’ in the results column.

The following statements may be added to your query ‘SELECT’ statement by adding the required text in the Sort and Criteria Pane under ‘Column’.

1. GETDATE()                Returns the current date details

SELECT GETDATE()                = 2007-05-15

2. DATEPART(item,date) Returns the specified part item of a date date as an integer.

SELECT

3. DATEDIFF(Item,dat1,dat2) Calculates the difference between the date parts dat1 and dat2 and returns the result as an integer in units specified by the value item.

SELECT

Item input can be as detailed above i.e. yy, qq, mm, dy, dd, dw, or wk.

SELECT

Note:  The number of units input may also be negative.  Therefore –30 in this example would return a date 30 days earlier.