Working With Query Criteria and Expressions

If you run a query after selecting the QueryFields (which executes a SQL statement, such as SELECT EMPLID, DEPTID from PS_QE_EMPLOYEE), the system retrieves all the data in those columns; that is, it retrieves the data from every row in the QueryRecord or records because there is no filter limiting the number of rows.

You can select which rows of data you want by adding selection criteria to the query.

This document assumes that you know how to use selection criteria. This section discusses working with the QueryCriteria and QueryExpression objects in the query API only.

Before you can add a new expression (either Expression 1 or Expression 2) to your QueryCriteria, you must set the type for the expression.

The following code example adds a new criteria, sets the type for the first expression, adds the first expression to the main select of the query definition, then does the same thing for the second expression.

&MyQuerySelect = &MyQuery.QuerySelect; 
&MyCriteria = &MyQuerySelect.AddCriteria(); 
 
/* make expression 1 a field */ 
&MyCriteria.Expr1Type = %Query_ExprField;  
 
/* add the ABSENCE_TYPE field */ 
&MyCriteria.AddExpr1Field("A", "ABSENCE_TYPE");  
 
/* make it not equal to */ 
&MyCriteria.Operator = %Query_CondNotEqual;  
 
/* Make expression 2 a constant */ 
&MyCriteria.Expr2Type = %Query_ExprConstant;  
&MyCriteriaExpr = &MyCriteria.AddExpr2Expression1(); 
&MyCriteriaExpr.Text = "VAC";

When you use any of the QueryCriteria methods to add either a new Expression 1 or Expression 2, you destroy the existing value.

In general, you should use the QueryCriteria methods to add a new Expression 1 or Expression 2 only when you're adding a new criteria to a query.

Which values are valid for the Expression 2 type (Expr2Type property) depend on the value of the Operator property.

The following table describes which Expr2Type values are valid with which values of Operator.

Operator

Expression 2

equal to

not equal to

greater than

not greater than

less than

not less than

Constant

Field

Expression

Subquery

Prompt

Exists

not exists

Subquery

Like

not like

Constant (with wildcards)

Prompt

is null

is not null

 

in tree

not in tree

Tree Option

Tree Prompt Option

Eff Date <=

Eff Date >=

Eff Date <

EffDate >

Field

Expression

Constant

Current Date

First Eff Date

Last Eff Date

 

in list

not in list

In list

Subquery

Between

Not Between

Const-Const

Const-Field

Const-Expr

Field-Const

Field-Field

Field-Expr

Expr-Const

Expr-Field

Expr-Expr

A drilling URL provides a clickable link in query results allowing a user to drill from the query results to another query, to another component, or to an external site. A drilling URL is defined as a special type of query expression. Like other query expressions, the user can set the drilling URL on the Edit Expression Properties page, or the drilling URL can be set in a PeopleCode program as follows:

/* Setting a drilling URL */

&aQueryExpr = &aQuerySelect.AddExpression(&sExprName);

/* Set the expression type to drilling URL */
&aQueryExpr.Type = %FieldType_URL;

/* Set the expression text and number from record fields */
/* The Text property must conform to expected formats    */
&aQueryExpr.Text = &rRecordExpr.QRYCRIT1EXPRTEXT.Value;
&aQueryExpr.ExpNum = &rRecordExpr.QRYCRIT1EXPRNUM.Value;

Note: Run-time processing of drilling URLs is backward compatible. Therefore, drilling URLs that were defined in previous releases are expanded correctly in the current release without the need to redefine them.

For a drilling URL, the Text property of the QueryExpression object must conform to one of three expected formats. The value of the Text property is not validated; therefore, it is the calling program’s responsibility to ensure that value is complete and correctly formatted. Each of the drilling URL types has a different format as follows:

  • The drilling URL for a component requires the following format:

    '/c/menu.component.market?Action=Usearch_key=unique_field:unique_field_URL_is_mapped_to'

    For example: 

    '/c/QE_SAMPLE_APPS.QE_DEPT_TBL.GBL?Action=U&DEPTID=A.DEPTID&SETID=A.SETID:A.SETID'

  • The drilling URL for a query requires the following format:

    '/q/?ICAction=ICQryNameURL={PUBLIC|PRIVATE}.query_name&unique_prompt_key=unique_field:unique_field_URL_is_mapped_to'

    For example:

    '/q/?ICAction=ICQryNameURL=PUBLIC.DESTINATION&BIND1=A.DEPTID:B.DEPTID'

  • The drilling URL for an external site requires the following format: 

    '/e/?url=[full_external_URL]:unique_field_URL_is_mapped_to'

    For example:

    '/e/?url=[http://www.yahoo.com]:A.SETID'