Query Classes

This chapter provides an overview of the query classes and discusses the following topics:

• Collections in the query classes.

• Life cycle of a query.

• Query classes hierarchy.

• Query API overview.

• Working with query criteria and expressions.

• Query monitor.

• Using query metadata.

• Running a query.

• Specifying the user’s language.

• Query security.

• Error handling with query classes.

• Understanding QueryOutputFields and QuerySelectedFields.

• Understanding having criteria.

• Data type of query objects.

• Scope of query objects.

• Query classes reference.

• Query classes examples.

Understanding Query Classes

You create queries using PeopleSoft Query Manager to extract the data you need from the PeopleSoft database. You can use the query classes in PeopleCode to create a new query, or to modify or delete an existing query. You can also use methods in the Query class to execute the query and have the result set returned as either a rowset or have it format and write the result set to a file. You can also use the query classes to create a SQL statement to be used with the SQL object. In fact, the query classes expose all of the attributes and methods needed by the PeopleSoft Query Manager and Query Viewer applications.

Creating or deleting a query object does not create or delete query information. You must call the appropriate method for that query object directly to create or delete database information, that is, the Create or Delete method.

All of the classes, and most of the properties and methods that make up the query classes, have a GUI representation in PeopleSoft Query Manager. This document assumes that the reader has working knowledge of PeopleSoft Query Manager.

There aren’t any external built-in functions for the query classes: objects are instantiated from other objects or from a session object.

See Also

Session Class

Getting Started with PeopleSoft Query

Collections in the Query Classes

A collection is a set of similar things, like a group of already existing queries or QueryRecords. As with everything else in the query classes, collections have a GUI representation in PeopleSoft Query.

For example, when you want to open a query, the search page returns a list of all the available queries. This is equivalent to using the FindQueries session class method to get a Query collection.

The following collections are part of the query classes:

• QuerySelect collection

• QueryDBRecord collection

• QueryDBRecordField collection

• Query collection

• QueryRecord collection

• QueryOutputField collection

• QuerySelectedField collection

• QueryCriteria collection

• QueryExpression collection

• QueryPrompt collection

Life Cycle of a Query

At runtime, there are certain things you want to do with a query, like creating one from scratch, updating the criteria for an existing query, running a query, and so on. The following is an overview of this process, and assumes the most common method to use the query API. These steps are expanded in other sections.

1. Invoke the GetQuery method on the PeopleSoft session object to get a query.

2. Either open the specific query you want using Open, or create a new query using Create.

3. Read the query statistics, or make changes as appropriate, adding records, field, criteria, and so on.

4. Save the changes.

5. (Optional) Use the RunToRowset method to run the query.

   Note. Be careful whenever you write a PeopleCode program that uses the RunToRowset method because this method could return a large amount of data that could potentially exceed the memory available. For this reason, RunToRowset should be used only when you know that the query being executed returns a reasonable amount of data, or be sure to use the MaxRows parameter to control the maximum amount of data that can be returned.

   Alternatively, you can:

   • Use the RunToFile method to execute a scheduled query.

   • Use the RunToString method to run a query and return the result as a formatted string.

6. Close the query.

7. If you want, you can navigate to PeopleSoft Query Manager, Query Viewer or the Query Designer to run the query.

Life cycle of a Query object

Query Classes Hierarchy

There are many different classes used with the query API. The following flowcharts illustrate all the different classes and how they're interrelated.

Query API class hierarchy (part 1 of 3)

Query API class hierarchy (part 2 of 3)

Query API class hierarchy (part 3 of 3)

Query API Overview

The query API is made up of many classes. The following are the primary parts, generally used when updating a query:

Query

A database query. You must get a query before you can access any of the other query classes. You can then create a new query and save it to the database, or open an existing query and modify it.

QuerySelect

Each query is composed of one or more select statements:

QueryRecords

The records that are part of an existing query definition. In PeopleSoft Query, these are the records listed on the query tab. Each QuerySelect has its own set of QueryRecords.

QueryOutputFields

The fields that you've selected to be part of the query definition. They're called output fields because when you run the query, these are the fields that make up the output columns.

In PeopleSoft Query, these are the fields listed on the Fields tab.

The collection of QueryOutputFields does not necessarily include all columns returned in query resultset. This collection only includes columns that have been added to query output collection using the query classes or by an end-user designing a query.

PeopleSoft Query can also add additional columns for related language processing and also for translate labels on fields, and so you cannot use the Query Output Collections as a way to discover all of the columns that are returned in the resultset or by executing the SQL generated from a query.

QuerySelectedFields

All the fields that make up the query definition. These include the fields selected as output fields, and fields added as QueryExpressions.

QueryCriteria

The selection criteria for the query. Each QueryCriteria object is made up of the following:

In PeopleSoft Query, a QueryCriteria is under the Criteria tab.

Expression can be made up of constant values, fields, subqueries, and so on.

See Also

Working With Query Criteria and Expressions

QueryDBRecords and QueryDBRecordFields

A QueryDBRecord is a record in the database that can be used as a QueryRecord. The list of records is controlled by security: the only records displayed as QueryDBRecords are records accessible by the user.

The QueryDBRecordFields are the fields that make up the QueryDBRecords.

In PeopleSoft Query, the QueryDBRecords are under the Record tab. After you click on the plus sign next to a record, the QueryDBRecordFields are displayed.

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 rowsof 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.

This section discusses how to:

• Set the expression type.

• Add new expressions.

• Add an operator and Expression 2 dependencies.

• Set a drilling URL.

See Also

Defining Selection Criteria

Setting the Expression Type

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";

Adding New Expressions

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.

Adding an Operator and Expression 2 Dependencies

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.

See Also

Operator

Setting a Drilling URL

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;

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'

See Also

AddTrackingURL

SetTrackingURL

Text

Type

Drilling URL in Oracle PeopleSoft Query

Query Monitor

The query classes provide many methods and properties for examining the QueryStatistics, which you can use to report on the average execution time, the last date and time the query was run, and so on.

You can view the statistics for a query before you save it to the database.

See Also

Getting Started with PeopleSoft Query

Using Query Metadata

There are two ways of accessing information about a query:

• The query classes (QueryOutputField, QueryRecord, and so on).

• The Metadata property.

If you use the Metadata property, the information is presented in a different format. It is also read-only. Using this property can be a quick and easy way to access information about a query.

Each Query Metadata object is a name-value pair. Name is an indicator of which Query Metadata property you're accessing, and Value is the value of that property.

While the value of each Query Metadata property is unique, the name may or may not be. For example, there is only one description (Descr) for each query, so there is only one Query Metadata property with name equal to Descr and value equal to the description for the query.

However, there may be more than one record for a query. A Query Metadata property exists for each record, with name equal to Record and value equal to one of the records in the query. The same is true for Input Param, Expression, Field, and Heading.

The following is a simple PeopleCode program to get the Query Metadata for a private query, ADDRESS_TEMP:

Local ApiObject &MyQuery, &MyMetacol, &MyMetadata; 
Local File &MyFile; 
 
&MyFile = GetFile("Metadata.Txt", "A"); 
 
&MyQuery = %Session.GetQuery(); 
 
&Rlst = &MyQuery.Open("ADDRESS_TEMP", False, 1); 
 
If &Rlst = 0 Then 
   &MyMetacol = &MyQuery.metadata; 
   &MyFile.WriteLine("Name              Value"); 
   &MyFile.WriteLine("-----------------------"); 
    
   For &i = 1 To &MyMetacol.count 
      &MyMetadata = &MyMetacol.item(&i); 
      &Name = &MyMetadata.name; 
      &Value = &MyMetadata.Value; 
      &MyFile.WriteLine(&Name | "              " | &Value); 
   End-For; 
    
Else 
   WinMessage("Open query not successful"); 
End-If;

Here is the sample output:

Name              Value 
----------------------- 
​Descr             Temporary address query for testing 
LongDescr               
Public/Private    QEDMO 
LastUpdDttm       2001-11-19-15.06.22.930000 
LastUpdOprId      QEDMO 
Record            QE_PERS_DATA 
Field             QE_EMPLID 
Field             QE_NAME 
Field             ADDRESS1 
Field             ADDRESS2 
Field             ADDRESS3 
Field             ADDRESS4 
Field             CITY 
Field             COUNTY 
Field             STATE 
Field             QE_ZIP 
Field             QE_COUNTRY 
Heading           ID 
Heading           Name 
Heading           Address 1 
Heading           Address 2 
Heading           Address 3 
Heading           Address 4 
Heading           City 
Heading           County 
Heading           St 
Heading           QE_ZIP 
Heading           QE_COUNTRY

Running a Query

With query API, you have the following options for running a query:

• Using the RunToRowset method.

  This method takes a QueryPrompt record as input and returns a PeopleCode rowset containing the query result. You should always use a standalone rowset (that is, one that was created using CreateRowset).

• Using the RunToFile method.

  This method takes a QueryPrompt record and a file name as input and writes the query result to the file.

• Using the RunToString method.

  This method takes a QueryPrompt record and writes the query result to a formatted string.

Note. For the query classes, all date, time, and datetime fields that are part of a query are now required fields when running a query.

Considerations Using the RunTo Methods

RunToRowset may require a lot of memory for the Rowset object; therefore, it also takes more processing time. PeopleSoft recommends that RunToRowset not be used for retrieving large result sets, on the order of 50,000 or more items.

All of the RunTo methods can be called to run a query before saving it—that is, it isn't necessary to first save the query.

The last parameter of all of the RunTo methods is the maximum number of rows to fetch.

• -1 returns all rows regardless of the setting on the security profile.

• 0 returns the maximum number of rows allowed by the security profile.

• >0 is the limit on the number of rows.

See Also

RunToFile

RunToRowset

RunToString

Specifying the User’s Language

For scheduled queries, the system uses the language specified in the user’s profile. It does not use the language selected during signon. The system also uses the International and Regional settings the user specified using My Personalizations. If no personal setting have been specified, the system uses the default installation international settings.

Note. Most PeopleSoft components can default to international settings from the browser if the user has not set any user specific settings. However, this is not available for scheduled queries or any Process Scheduler processes.

Query Security

Security is critical for your business data. Typically, you don’t want everyone in your company to have access to all the data. All the standard security features used with PeopleSoft applications are integrated in the PeopleSoft Query, as well as the query classes.

In addition, you can use the QuerySecurityProfile Class to view the current user's security profile for PeopleSoft Query. This class doesn't contain any methods, and all the properties are read-only.

See Also

QuerySecurityProfile Class

PeopleSoft Query Security

Understanding PeopleSoft Security

Error Handling With Query Classes

All errors for the query classes, like the other APIs, are logged in the PSMessages collection, instantiated from a session object. In addition, some methods return error codes. See the individual method description to see if it returns anything.

The query classes log errors "interactively", that is, as they happen. For example, suppose you specified an invalid query name. The error would be logged in the PSMessages collection as soon as you executed the GetQuery method.

When to check for errors is application-specific. However, if you check for errors after every assignment, you may see a performance degradation.

PeopleSoft recommends that you check for invalid prompts after using any of the following methods or properties:

• Save, RunToRowset, or RunToFile Query class methods.

• SQL, RuntimePrompts, or Prompts Query class properties.

• Any of the methods or properties in the QueryPrompts collection.

The easiest way to check for errors is to check the number of messages in the PSMessages collection, using the Count property. If the Count is 0, there are no errors.

Local ApiObject &MySession; 
Local ApiObject &ERRORCOL; 
Local ApiObject &Query, &QueryList; 
 
&MySession = %Session; 
 
If &MySession Then 
   /* connection is good */ 
 
   &QueryList = &MySession.SearchPublicQueries(%Query_ListQuery, ⇒
%Query_FindName, "%", True); 
 
   For &I = 1 to &QueryList.Count 
      &Query = &QueryList.Item(&I); 
 
      /* Do processing */ 
       
      ​/* Do error checking */ 
    
      &ERRORCOL = &MySession.PSMessages; 
      If (&ERRORCOL.Count <> 0) Then 
         /* errors occurred - do processing */ 
      Else 
         /* no errors */ 
      End-If; 
​ 
   End-For; 
Else 
   /* do processing for no connection */ 
End-If;

See Also

Error Handling

Understanding QueryOutputFields and QuerySelectedFields

If you select a field from a Query Record, it becomes a QueryOutputField, that is, it becomes one of the columns in the SQL output. QuerySelectedFields consist of displayed fields and Query Expression Fields.

QuerySelectedFields and QueryOutputFields have all the same methods and properties, so in this documentation, they're described together under the heading QueryField.

The collection of QueryOutputFields does not necessarily include all columns returned in query resultset. This collection only includes columns that have been added to query output collection using the query classes or by an end-user designing a query.

PeopleSoft Query can also add additional columns for related language processing and also for translate labels on fields, and so you cannot use the Query Output Collections as a way to discover all of the columns that are returned in the resultset or by executing the SQL generated from a query.

Understanding Having Criteria

You can have where (simple) criteria or having criteria in a query. In most cases, you have a WHERE clause only—that is, simple criteria. Having criteria is only used in some special cases of aggregation queries. They represent the HAVING clause of the SQL statement, similar to the following:

select A.DEPTID, COUNT(A.EMPLID)

from PS_QE_EMPLOYEE A

group by A.DEPTID

having COUNT(A.EMPLID)>5

Having criteria are separated from simple criteria. They're accessed as follows:

• Having criteria are accessed using either the AddHavingCriteria QuerySelect method or HavingCriteria QuerySelect property

• Simple criteria are accessed using either the AddCriteria QuerySelect method or the Criteria QuerySelect property.

• However, the having criteria and the simple criteria have all the same methods and properties, so in this documentation, they're described together under the heading QueryCriteria.

See Also

Defining HAVING Criteria

Data Type of Query Objects

All query objects, like a query collection, a QueryDBRecord, a QueryExpression, and so on, are declared as type ApiObject. For example,

Global ApiObject &MyQuery;

Local ApiObject &MyExpression;

Scope of Query Objects

All query objects can be instantiated from PeopleCode only.

You can use this object anywhere you have PeopleCode—that is, in an application class, Application Engine PeopleCode, record field PeopleCode, and so on.

You can only instantiate a query object from a Session object. You must instantiate the Session object before you can instantiate a query object or query collection. Use the %Session system variable to connect to the existing session.

Local ApiObject &QueryList; 
Local ApiObject &MySession; 
 
&MySession = %Session; 
 
If &MySession <> Null Then 
   /* connection is good */ 
   /* Search for public queries of type QUERY, search both the name and*/
   /* description for the pattern MyQ%, and do a case insensitive search */

   &QueryList = &MySession.SearchPublicQueries(1, 3, "MyQ%", False); 
Else 
   /* do error processing */ 
End-if;

Query Classes Reference

This section provides a reference to the following topics:

• Session class methods in the query API.

• Query collection.

• Query class.

• QuerySelect collection.

• QuerySelect class.

• QueryRecord collection.

• QueryRecord class.

• QueryField collection.

• QueryField class.

• QueryCriteria collection.

• QueryCriteria class.

• QueryExpression collection.

• QueryExpression class.

• QueryList class.

• QueryListValue class.

• QueryRecordHierarchy collection.

• QueryRecordHierarchy class.

• Query Metadata collection.

• Query Metadata class.

• QueryStatistics class.

• QuerySecurityProfile class.

• QueryDBRecord collection.

• QueryDBRecord class.

• QueryDBRecordField collection.

• QueryDBRecordField class.

• QueryPrompt collection.

• QueryPrompt class.

Session Class Methods in the Query API

The following methods are part of the query API. However, they're used with a Session object.

See Also

Session Class.

AdvancedSearchQueries

Syntax

AdvancedSearchQueries(GetFavorites, QueryName, QueryNameOp, Descr, DescrOp, FolderName, FolderNameOp, RecordName, RecordNameOp, FieldName, FieldNameOp, TreeName, TreeNameOp, QueryType, OwnerType, CaseSensitive)

Description

Use the AdvancedSearchQueries method to do more complex searches for queries.

Using Search Operators

All of the parameters for this method work in pairs, with the value in the first of the paired parameters further distinguished by the search operator used in the second parameter. For example, the value specified in FieldName is paired with the value specified in FieldNameOp.

All of the search operator parameters use the following values. Note that the format of the value of the first parameter is sometimes affected by the value of the search operator parameter.

Parameters

The values for QueryType can be as follows:

Returns

A reference to a Query collection containing zero or more queries.

Note. If the result set contains more than 300 rows, only the first 300 rows are returned.

AdvancedSearchRecords

Syntax

AdvancedSearchRecords(RecordName, RecordNameOp, Descr, DescrOp, FieldName, FieldNameOp, TreeName, TreeNameOp, CaseSensitive)

Description

Use the AdvancedSearchRecords method to do more complex searches for records.

Security applies to the results of this list, that is, you have access to all the records your user ID (permission list) allows you to access.

Using Search Operators

All of the parameters for this method work in pairs, with the value in the first of the paired parameters further distinguished by the search operator used in the second parameter. For example, the value specified in FieldName is paired with the value specified in FieldNameOp.

All of the search operator paramerters use the following values. Note that the format of the value of the first parameter is sometimes affected by the value of the search operator parameter.

Parameters

Returns

A reference to a QueryDBRecord collection containing zero or more records.

Note. If the result set contains more than 300 rows, only the first 300 rows are returned.

FindQueryDBRecords

Syntax

FindQueryDBRecords()

Description

The FindQueryDBRecords method returns a reference to a QueryDBRecord collection, filled with zero or more records.

Security applies to the results of this list, that is, you have access to all the records your user ID (permission list) allows you to access.

Parameters

None.

Returns

A reference to a QueryDBRecord collection containing zero or more records.

See Also

QueryDBRecord Collection, QueryDBRecord Class.

FindQueries

Syntax

FindQueries()

Description

The FindQueries method returns a reference to a Query collection, filled with zero or more queries.

FindQueries Considerations

FindQueries returns both public and private queries. It depends on your database whether the private query is returned first or the public query. If you have two queries with the same name, it depends on your database whether the first use of Item returns the private or the public query.

Parameters

None.

Returns

A reference to a Query collection containing zero or more queries.

Example

In the following example, all available queries are returned:

Local ApiObject &MySession; 
Local ApiObject &MyList; 
 
&MySession = %Session
&MyList = &MySession.FindQueries();

See Also

Open, FindQueriesDateRange, Query Collection.

FindQueriesDateRange

Syntax

FindQueriesDateRange(StartDateString, EndDateString)

Description

The FindQueriesDateRange method returns a reference to a Query collection, filled with zero or more queries that match the specified date range.

FindQueriesDateRange Considerations

FindQueriesDateRange returns both public and private queries. It depends on your database whether the private query is returned first or the public query. If you have two queries with the same name, it depends on your database whether the first use of Item returns the private or the public query.

Parameters

Returns

A reference to a Query collection containing zero or more queries.

Example

Local ApiObject &MySession, &QueryList; 
 
&MySession = %Session; 
 
&Start = GetField(VOLUN_ACT_WRK.START_DT_STR).Value; 
&End = GetField(VOLUN_ACT_WRK.END_DT_STR).Value; 
 
&QueryList = &MySession.FindQueriesDateRange(&Start, &End);

See Also

FindQueries, Open, Query Collection.

GetQuery

Syntax

GetQuery()

Description

The GetQuery method returns an empty query object. After you have an empty query object, you can use it to open an existing query (using the Open method) or to create a new query definition (using the Create method).

Parameters

None.

Returns

A reference to an empty query object if successful, NULL otherwise.

Example

&MyQuery = &MySession.GetQuery();

If &MyQuery.Open("PHONELIST") Then

See Also

Session (query API) class: FindQueries method, FindQueriesDateRange method, Open method, Create method.

GetQuerySecurityProfile

Syntax

GetQuerySecurityProfile()

Description

Use GetQuerySecurityProfile to return the current user's security profile for PeopleSoft Query. You can then use the QuerySecurityProfile properties to determine if the user can modify queries, the maximum number of rows to fetch for this user, and so on.

Parameters

None.

Returns

A reference to a QuerySecurityProfile if successful, NULL otherwise.

Example

&MySecProfile = %Session.GetQuerySecurityProfile(); 
If &MySecProfile.CanModifyQuery Then 
   /* do some processing */ 
End-If;

See Also

QuerySecurityProfile Class.

SearchQueryDBRecords

Syntax

SearchQueryDBRecords(SearchType, Pattern, CaseSensitive)

Description

The SearchQueryDBRecords method returns a reference to a QueryDBRecord collection, filled with zero or more records.

Security applies to the results of this list, that is, you have access to all the records your user ID (permission list) allows you to access.

You can use wildcard characters % and _ when searching. % means find all characters, while _ means find a single character. For example, if you wanted to find all queries that started with the letter M, use "M%" for Pattern. To find either DATE or DATA, use "DAT_" for Pattern.

These characters can be escaped (that is, ignored) using a \. For example, to search for a query that contains the character %, use \% in Pattern.

If Pattern is an empty string, this method retrieves all queries of the specified type (that is, specifying "" for Pattern is the same as specifying "%").

Parameters

The values for SearchType can be as follows:

Returns

A reference to a QueryDBRecord collection containing zero or more records.

Example

Local ApiObject &MySession, &DBRecList; 
 
&MySession = %Session; 
 
DBRecList = &MySession.SearchQueryDBRecords(%Query_FindName, "A%", False);

See Also

Session (query API) class: FindQueryDBRecords method.

SearchPrivateQueries

Syntax

SearchPrivateQueries(QueryType, UserId, SearchType, Pattern, CaseSensitive)

Description

The SearchPrivateQueries method returns a reference to a Query collection, filled with zero or more Private queries that match the specified SearchType, UserId, Pattern, and CaseSensitive choice

Parameters

The values for QueryType can be as follows:

The values for SearchType can be as follows:

Returns

A reference to a Query collection containing zero or more queries.

Example

The following example retrieves all private queries of type Query which start with A and do a case-insensitive search, that is, get all queries starting with A or a.

Local ApiObject &MySession, &DBRecList; 
 
&MySession = %Session; 
 
DBRecList = &MySession.SearchPrivateQueries(%Query_ListQuery, %UserId, ⇒
%Query_FindName, "A%", False);

See Also

Session (query API) class: FindQueries method, FindQueriesDateRange method, SearchPublicQueries method.

SearchPublicQueries

Syntax

SearchPublicQueries(QueryType, SearchType, Pattern, CaseSensitive)

Description

The SearchPublicQueries method returns a reference to a Query collection, filled with zero or more Public queries that match the specified SearchType, Pattern, and CaseSensitive choice.

Parameters

The values for QueryType can be as follows:

The values for SearchType can be as follows:

Returns

A reference to a Query collection containing zero or more queries.

Example

The following example retrieves all public queries of type Query that start with A and does a case-insensitive search, that is, get all queries starting with A or a.

Local ApiObject &MySession, &DBRecList; 
 
&MySession = %Session; 
 
DBRecList = &MySession.SearchPublicQueries(%Query_ListQuery, ⇒
%Query_FindName, "A%", False);

See Also

Session (query API) class: FindQueries method, FindQueriesDateRange method, SearchPrivateQueries method.

Query Collection

A query collection is returned from the following session methods:

• AdvancedSearchQueries

• FindQueries

• FindQueriesDateRange

• SearchPrivateQueries

• SearchPublicQueries

See Also

AdvancedSearchQueries

FindQueries

FindQueriesDateRange

SearchPublicQueries

SearchPrivateQueries

Query Collection Methods

In this section, we discuss each query collection method.

First

Syntax

First()

Description

The First method returns the first Query object in the Query collection.

Parameters

None.

Returns

A reference to a Query object if successful, NULL otherwise.

Example

&MyQuery = &MyCollection.First();

Item

Syntax

Item(number)

Description

The Item method returns the Query object that exists at the number position in the Query collection.

Item Considerations

FindQueries and FindQueriesDateRange return both public and private queries. It depends on your database whether the private query is returned first or the public query. If you have two queries with the same name, it depends on your database whether the first use of Item returns the private or the public query.

Parameters

Returns

A reference to a Query object if successful, NULL otherwise.

Example

For &I = 1 to &QueryColl.Count; 
   &MyQuery = &QueryColl.Item(&I); 
   /* do processing */ 
End-For;

ItemByName

Syntax

ItemByName(Name)

Description

The ItemByName method returns the Query object with the name Name.

Parameters

Returns

A reference to a Query object if successful, NULL otherwise.

Example

&MyQuery = &MyCollection.ItemByName("PHONELIST");

Next

Syntax

Next()

Description

The Next method returns the next Query object in the Query collection. You can use this method only after you have used the First method: otherwise the system doesn’t know where to start.

Parameters

None.

Returns

A reference to a Query object if successful, NULL otherwise.

Example

&MyQuery = &MyCollection.Next();

Query Collection Property

In this section, we discuss the Count query collection property.

Count

Description

This property returns the number of Query objects in the Query collection, as a number.

This property is read-only.

Example

&COUNT = &MY_COLLECTION.Count;

Query Class

A query object is returned from the following:

• The GetQuery session method.

• The Query collection methods First, Item, ItemByName, or Next.

See Session (query API) class: GetQuery method.

See Query Collection.

Query Class Methods

In this section, we discuss each Query class method in alphabetical order.

AddPrompt

Syntax

AddPrompt(PromptName)

Description

The AddPrompt method adds a prompt with the given name to the query definition. This method returns a new QueryPrompt object that you can then use to specify details about the prompt.

Note. Prompt names are not checked for uniqueness. Each new prompt is just added to the list of prompts.

Parameters

Returns

A reference to a QueryPrompt object.

See Also

Query class: DeletePrompt method, Prompts property, QueryPrompt Class .

AddQuerySelect

Syntax

AddQuerySelect()

Description

Use the AddQuerySelect method to add the first Query Select statement into the query definition. This method returns the instance of the newly created QuerySelect.

Parameters

None.

Returns

A reference to a QuerySelect object. If the query already contains the Main Select, it returns NULL.

See Also

QuerySelect Collection.

AddTrackingURL

Syntax

AddTrackingURL(URLString)

Description

Use this method to define the base URL used to construct a fully qualified drilling URL. A base URL consists of the following elements:

http://server_name/servlet_name/site_name/portal_name/node_name

For example:

http://server.mycompany.com:8080/psp/ps_2/EMPLOYEE/EMP_LOCAL

The string passed in as the base URL is not validated; therefore, it is the developer’s responsibility to ensure that the value is complete and correctly formatted.

This method is used when a program does not have context to identify the site, portal, and node. For example, batch programs such as Application Engine programs do not operate in the context of a site, portal, and node. Consequently, prior to invoking an Application Engine program, the calling program needs to define and store the base URL so that the Application Engine program can use this stored value with the AddTrackingURL method.

Parameters

Returns

None.

Example

The ExecQry step of the PSQUERY Application Engine program invokes the AddTrackingURL method using a value stored in the run control table prior to executing the query.

&aQry = %Session.GetQuery();
If &aQry.Open(PSQUERY_AET.QRYNAME, &bPublic, False) = 0 Then

   &aQry.AddTrackingURL(PSQUERY_AET.URL);
   ...
   &Result = &aQry.RunToFile(&rcdQryPrompts, &sOutFile, %OutDestFormat, 0);
End-if;

See Also

SetTrackingURL

Setting a Drilling URL

Drilling URL in Oracle PeopleSoft Query

Close

Syntax

Close()

Description

The Close method closes the query, freeing the memory associated with that object, and discarding any changes made to the query since the last save. The Close method can be used only on an open query, not a closed query. This means you must have opened the query with the Open or Create methods before you can close it. To save any changes, you must use the Save method before using Close.

It’s very important to close your query when you’re finished processing. Canceling out of a page does not close a query. You may receive error messages every other time you run your program if you haven’t closed your queries.

Parameters

None.

Returns

None.

See Also

Query class: Open method, Save method, Create method.

CopyPrivateQuery

Syntax

CopyPrivateQuery(QueryName, QryType, TargetUserID)

Description

The CopyPrivateQuery method copies the query from the current user to a user specified by the target user ID.

Parameters

The values for QryType can be as follows:

Returns

0 if successful.

Create

Syntax

Create(QueryName, Public, Type, Description, LongDescription)

Description

The Create method creates a new query, based on the parameters passed with the method. The specified query must be a new query.

Warning! If you specify the name of a query that already exists, the existing query is overwritten by the new query.

The Create method can be used only with a closed query, it cannot be used on an open query.

After you create a new query, you don't have to open it with the Open method. The existing query object points to the new query.

Creating a new query doesn't create the query in the database. You must save the query (with the Save method) to commit it to the database.

Parameters

The values for Type can be as follows:

Returns

An integer value: 0 means the query was created successfully.

Example

/* Use the existing session */ 
 
&MySession = %Session; 
 
&MyQry = &MySession.GetQuery(); 
 
/* create a new query : public type-User */ 
&Rslt = &MyQry.Create("MYQUERY", True, %Query_Query, "My Query", ⇒
"My first Query"); 
 
If &Rslt = 0 Then 
   /* Query created successfully */ 
Else 
   /* do error processing */ 
End-If;

See Also

GetQuery, Close, Save.

Delete

Syntax

Delete()

Description

The Delete method deletes the specified query from the database. The Delete method can be used only with an open query, it cannot be used on a closed query. Before you use the Delete method, you must explicitly open the query object to be deleted (with either the Open or Create method.)

Parameters

None.

Returns

An integer value: 0 means the query was deleted successfully.

See Also

Query class: Save method.

DeletePrompt

Syntax

DeletePrompt(PromptNumber)

Description

The DeletePrompt method deletes a prompt from a query definition.

Parameters

Returns

An integer value: 0 means the query was deleted successfully.

See Also

Query class: AddPrompt method, Prompts property, QueryPrompt Class .

FindExpression

Syntax

FindExpression(ExpressionNumber)

Description

Use the FindExpression method to search the query definition for an expression with the given expression number. Although expressions are associated with the QuerySelect in which they are defined, PeopleSoft Query doesn't qualify expressions by Select number. Therefore, each expression has a unique number across all QuerySelect objects.

Parameters

Returns

A QueryExpression object if successful, NULL otherwise.

See Also

Query class: AddPrompt method, Prompts property, QueryPrompt Class .

FormatBinaryResultString

Syntax

FormatBinaryResultString(&Rowset, Output_Format, Start_Row, End_Row)

Description

Use the FormatBinaryResultString method to generate a string containing the content of the input (the rowset) in XLS format. You can specify the range of rows to be in the rowset to be used as input. The rowset object is created using the RunToRowset method.

Parameters

The values for Output_Format can be as follows:

Returns

A binary object containing the formatted output.

Example

/* Use RowCount, not ActiveRowCount, to get the total number of rows. */

&End = &MyRowset.RowCount;

&FormString = &MyQuery.FormatBinaryResultString (&MyRowset, %Query_XLS, 1, &End);

See Also

Query class: RunToFile method, RunToRowset method.

FormatResultString

Syntax

FormatResultString(&Rowset, Output_Format, StartRow, EndRow)

Description

Use the FormatResultString method to generate a string containing the content of the input (the rowset) in HTML, PDF, XLS, or CSV format. You can specify the range of rows to be in the rowset to be used as input. The rowset object is created using the RunToRowset method.

Parameters

The values for Output_Format can be as follows:

Returns

A string containing the formatted output.

Example

/* Use RowCount, not ActiveRowCount, to get the total number of rows. */

&End = &MyRowset.RowCount;

&FormString = &MyQuery.FormatResultString(&MyRowset, %Query_TXT, 1, &End);

See Also

Query class: RunToFile method, RunToRowset method.

GetTreePromptCount

Syntax

GetTreePromptCount()

Description

Use the GetTreePromptCount method to get the number of tree prompts if available in the query.

Parameters

None.

Returns

A number that indicates the count of tree prompts used in the query. -1 in case of an error.

Example

In this example, the ExecQry step of the PSQUERY Application Engine program invokes the GetTreePromptCount() method using a value stored in the run control table prior to executing the query.

&aQry = %Session.GetQuery();
If &aQry.Open(PSQUERY_AET.QRYNAME, &bPublic, False) = 0 Then
   &nTreePromptCount = &aQry.GetTreePromptCount();
End-if;

Open

Syntax

Open(QueryName, Public, Update)

Description

The Open method opens the query object specified by the parameters. The Open method can be used only with a closed query, it cannot be used on an open query. You cannot read or set any properties of a query until after you open it.

Considerations for Opening Different Types of Queries

When you use the Open method, the system tries to open queries according to the type of query. The following is the order used for searching for the query to open:

1. Query (User)

2. View

3. Role

4. Process

5. Archive

All query names are unique. You can't have two queries with the same name, just of different types.

Parameters

Returns

An integer value: 0 means the query was opened successfully.

See Also

GetQuery, Close, Save.

Rename

Syntax

Rename(NewQueryName)

Description

The Rename method renames the existing query definition with NewQueryName. The Rename method can be used only with an open query, it cannot be used on a closed query. Before you use the Rename method, you must explicitly open the query object to be renamed (with either the Open or Create method.)

Note. The Rename method takes place immediately. You don't have to save the query for the rename to occur.

Parameters

Returns

An integer value: 0 means the query was renamed successfully.

See Also

Query class: Open method, Close method, Save method.

RunToFile

Syntax

RunToFile(&PromptRecord, Destination, OutputFormat, MaxRows)

Description

Use the RunToFile method to execute the Query and return the result to the file specified with Destination. The query should be an existing query in the database, or it should have been saved using the Save method.

Because a Query may have runtime prompts (that is, criteria defined using Prompt), this method requires those values to be passed in when you execute this method. PromptRecord is a PeopleCode record object containing the prompt values as fields in the record. You can use the PromptRecord property to obtain this object.

Destination must include the absolute path name of where the file is to be created.

If the specified subdirectory does not exist, this method does not automatically create them for you, and you receive an error.

If you specify HTML as the output format, the PeopleSoft Query style sheet PSQUERYSTYLEDEF is used for formatting the output.

See Also

Creating Style Sheet Definitions.

Parameters

The values for OutputFormat can be as follows:

Returns

Returns 0 if successful.

Example

To run a query using the query API RunToFile method:

1. Open the query.

   &aRunQry = %Session.GetQuery();
   
   &aRunQry.Open(&sQryName, False, False);

2. Obtain the PromptRecord for the query.

   &aQryPromptRec = &aRunQry.PromptRecord;

   This instance of the PromptRecord can be passed to the PeopleCode Prompt function to prompt the user for the runtime values, as follows:

   &nResult = Prompt(&strQryName | " Prompts", "", &aQryPromptRec);

3. Run the query.

   Now that you have the runtime values, the query can be run, as follows:

   &aRowSet = &aRunQry.RunToFile(&aQryPromptRec, "c:\temp\QueryOutput.html", ⇒
   %Query_PDF, 0);

4. Access the data of the rowset.

   You can now manipulate the rowset using the data buffer access methods and properties:

   &aRowSet(&i).GetRecord(1).GetField(&j).Value

The following is a complete sample code example:

Local Rowset &aRowSet; 
   Local Row &aRow; 
   Local Record &aQryPromptRec; 
   Local Record &aRec; 
   Local ApiObject &aRunQry; 
   &strHTML = ""; 
   &aRunQry = %Session.GetQuery(); 
   If (&aRunQry.Open(&sQryName, False, False) <> 0) Then 
      &strHTML = "Error in opening query"; 
   Else 
      &aQryPromptRec = &aRunQry.PromptRecord; 
      &strQryName = &aRunQry.Name; 
   If &aQryPromptRec <> Null Then 
      &nResult = Prompt(&strQryName | " Prompts", "", &aQryPromptRec); 
   End-If; 
   If (&aRunQry.RunToFile(&aQryPromptRec, "c:\temp\" | ⇒
&aRunQry.Name, 3, 0) = 0) Then 
      &strHTML = "Resultset saved into file successfully."; 
   Else 
      &strHTML = "Failed to save Resultset into file."; 
   End-If; 
End-If;

See Also

Query class: RunToRowset method, PromptRecord property.

Prompt

Accessing the Data Buffer

RunToRowset

Syntax

RunToRowset(&PromptRecord, MaxRows)

Description

Use the RunToRowset method to execute the Query and return the result to a rowset. The query should be an existing query in the database, or it should have been saved using the Save method.

Because a Query may have runtime prompts (that is, criteria defined using Prompt), this method requires those values to be passed in when you execute this method. PromptRecord is a PeopleCode record object containing the prompt values as fields in the record. The PromptRecord property can be used to obtain this object.

Parameters

Returns

If successful, the query result is returned as a populated PeopleCode rowset. If the query wasn't successful, the method returns NULL. If unsuccessful, check the PSMessages collection for errors.

Example

To run a query using the query API RunToRowset method:

1. Open the Query.

   &aRunQry = %Session.GetQuery();
   
   &aRunQry.Open(&sQryName, False, False);

2. Get the PromptRecord for the Query.

   &aQryPromptRec = &aRunQry.PromptRecord;

   This instance of the PromptRecord can be passed to the PeopleCode Prompt function to prompt the user for the runtime values, as follows:

   &nResult = Prompt(&strQryName | " Prompts", "", &aQryPromptRec);

3. Run the query.

   Now that you have the prompt values, you can run the query:

   &aRowSet = &aRunQry.RunToRowset(&aQryPromptRec, 0);

4. Access the data of the rowset.

   You can now manipulate the rowset using the data buffer access methods and properties:

   &aRowSet(&i).GetRecord(1).GetField(&j).Value

The following is a complete sample code example:

   Local Rowset &aRowSet; 
   Local Row &aRow; 
   Local Record &aQryPromptRec; 
   Local Record &aRec; 
   Local ApiObject &aRunQry; 
   &strHTML = ""; 
   &aRunQry = %Session.GetQuery(); 
   If (&aRunQry.Open(&sQryName, False, False) <> 0) Then 
      &strHTML = "Error in opening query"; 
   Else 
      &aQryPromptRec = &aRunQry.PromptRecord; 
      &strQryName = &aRunQry.Name; 
   If &aQryPromptRec <> Null Then 
      &nResult = Prompt(&strQryName | " Prompts", "", &aQryPromptRec); 
      &aRowSet = &aRunQry.RunToRowset(&aQryPromptRec, 0); 
         If &aRowSet <> Null Then 
               &strHTML = &strHTML | "<table cellpadding='2' ⇒
cellspacing='0' width='90%'>"; 
               &aRec = &aRowSet(1).GetRecord(1); 
               &QrySelOutputFldCol = &aRunQry.QuerySelect.QueryOutputFields; 
               If &QrySelOutputFldCol <> Null Then 
                  &strHTML = &strHTML | "<TR><TD>"; 
                  &strHTML = &strHTML | "<table border='1' cellpadding='2'⇒
 cellspacing='0' width='100%'>"; 
                  &strHTML = &strHTML | "<TR><TH>&nbsp;</TH>"; 
                  For &j = 1 To &QrySelOutputFldCol.count 
                     &strHTML = &strHTML | "<TH><B>" | &QrySelOutputFldCol.Item⇒
(&j).LongName | "</B></TH>"; 
                  End-For; 
                  &strHTML = &strHTML | "</TR>"; 
                  &strHTML = &strHTML | "</TD></TR>"; 
               End-If; 
               &strHTML = &strHTML | "<TR><TD>"; 
               If &aRowSet.RowCount > 0 Then 
                  For &i = 1 To &aRowSet.RowCount 
                     &aRow = &aRowSet(&i); 
                     &strHTML = &strHTML | "<TR>" | "<TD>" | &i | "</TD>"; 
                     For &j = 1 To &aRow.GetRecord(1).FieldCount 
                        &strHTML = &strHTML | "<TD>" | &aRow.GetRecord(1).GetField⇒
(&j).Value | "</TD>"; 
                     End-For; 
                     &strHTML = &strHTML | "</TR>"; 
                  End-For; 
               Else 
                  &strHTML = &strHTML | "No records retrieved."; 
               End-If; 
               &strHTML = &strHTML | "</TD></TR>"; 
               &strHTML = &strHTML | "</TABLE>"; 
               &strHTML = &strHTML | "</TABLE>"; 
            Else 
               &strHTML = "Failed to retrieve result set."; 
            End-If; 
      End-If; 
   End-If;

See Also

Query class: RunToFile method, PromptRecord property.

Prompt

Accessing the Data Buffer

RunToString

Syntax

RunToString(&PromptRecord, ChunkSize, OutputFormat, MaxRows)

Description

Use the RunToString method to execute the query and return the result as a formatted string.

When “chunking” is active, RunToString is intended to be called in a recursive fashion until the result set has been completely traversed. A Boolean property, MoreRowsAvailable, is included in the Query class to control recursive execution of this method.

See Query class: MoreRowsAvailable property.

Parameters

The values for Output_Format can be as follows:

Returns

A string containing the formatted output. If an error occurs, an empty string will be returned.

If there are no errors, but no data is in the query result set, the string will have system-defined header and footer information, but no rows will be present.

Example

The following example runs a query without chunking.

&queryname = "XRFWIN";

rem &querytype = %Query_XLS;
rem &querytype = %Query_PDF;
rem &querytype = %Query_HTML;
rem &querytype = %Query_TXT;
&querytype = %Query_XML_XmlP;
rem &querytype = %Query_XML_WebRowset;


&aRunQry = %Session.GetQuery();
If (&aRunQry.Open(&queryname, False, False) <> 0) Then
   MessageBox(0, "", 0, 0, "Error opening query");
Else
   &aQryPromptRec = &aRunQry.PromptRecord;
   
   &chunksize = 0;
   
   &filename = "C:\QueryWork850\output\" | &querytype | "_runtostring.xml";
   
   &resultString = &aRunQry.RunToString(&aQryPromptRec, &chunksize, &querytype, 0);
   
   &myfile = GetFile(&filename, "w", %FilePath_Absolute);
   &myfile.writestring(&resultString);
   &myfile.close();
   
   MessageBox(0, "", 0, 0, "Success!");
   
End-If;

The following example runs a query with chunking.

&queryname = "XRFWIN";
rem &querytype = %Query_XLS;
rem &querytype = %Query_PDF;
rem &querytype = %Query_HTML;
rem &querytype = %Query_TXT;
&querytype = %Query_XML_XmlP;
rem &querytype = %Query_XML_WebRowset;


&aRunQry = %Session.GetQuery();
If (&aRunQry.Open(&queryname, False, False) <> 0) Then
   MessageBox(0, "", 0, 0, "Error opening query");
Else
   &aQryPromptRec = &aRunQry.PromptRecord;
   &counter = 0;
   
   &chunksize = 1000;
   
   Repeat
      &counter = &counter + 1;
      &filename = "C:\QueryWork850\output\" | &querytype | &counter | ⇒
"_runtostring.xml";

      &resultString = &aRunQry.RunToString(&aQryPromptRec, &chunksize, ⇒
&querytype, 0);      
      
      If (Len(&resultString) > 0) Then
         
         &myfile = GetFile(&filename, "w", %FilePath_Absolute);
         &myfile.writestring(&resultString);
         &myfile.close();
         
      Else
         
         /* edge condition; if there are no more rows AND the */
         /* returned string is empty this is not an error.    */
         
         If (&aRunQry.MoreRowsAvailable) Then
            /* we have an error... Yikes! */
            MessageBox(0, "", 0, 0, "Something bad has happened!");
         Else
            /* no worries, just ignore it */
         End-If;
         
      End-If
   Until (Not &aRunQry.MoreRowsAvailable);
   
   MessageBox(0, "", 0, 0, "Success!");
   
End-If;

See Also

MoreRowsAvailable

Save

Syntax

Save()

Description

The Save method writes any changes to the existing query to the database.

The Save method can be used only on an open query, not on a closed query. This means you must have opened the query with the Open method before you can save it.

The query object remains open after executing Save. You must execute the Close method on the object before it is closed and the memory freed.

Note. If you’re calling the query API from an Application Engine program, the data won’t actually be committed to the database until the Application Engine program performs a COMMIT.

Parameters

None.

Returns

An integer value: 0 means the query was saved successfully.

See Also

Query class: Open method, Close method.

SetTrackingURL

Syntax

SetTrackingURL(ExpressionText, ExpressionNumber)

Description

Use the SetTrackingURL method to re-establish the drilling URL if the current execution context is different from the context in which the drilling URL was initially set.

For example, a drilling URL is set as a query expression by the program that executes the query. After query execution, a different program—an iScript program—allows the user to download the query results to an Excel spreadsheet. This iScript program needs to include the drilling URL in the spreadsheet data. In order for the iScript program to have access to the drilling URL query expression values, these values must be defined as global objects by the program that executes the query. Then, the iScript program can re-establish the drilling URL by calling the SetTrackingURL method.

Parameters

Returns

None.

Example

If &rsURLList <> Null And
      &rsURLList.ActiveRowCount >= 1 And
      All(&rsURLList(1).QRY_URL_WRK.QRYCRIT1EXPRTEXT.Value) Then
   For &nCount = 1 To &rsURLList.ActiveRowCount;
      &rRecordExpr = &rsURLList(&nCount).QRY_URL_WRK;
      &QryObj.SetTrackingURL(&rRecordExpr.QRYCRIT1EXPRTEXT.Value, ⇒
&rRecordExpr.QRYCRIT1EXPRNUM.Value);
   End-For;
End-If;

See Also

AddTrackingURL

Setting a Drilling URL

Drilling URL in Oracle PeopleSoft Query

Query Class Properties

In the following section, we discuss each Query class property.

Approved

Description

This property returns a string indicating whether a query is approved, unapproved, or modified. This is useful for query administration. The values are:

This property is read-write.

ApproveOprId

Description

Note. This property has been deprecated, and remains for backward compatibility only. Use the ApproveUserId property instead.

This property returns a string containing the user ID of the user who approved the query. This can be useful for query administration.

This property is read-write.

See Also

Query class: ApproveUserId property.

ApproveUserId

Description

This property returns a string containing the user ID of the user who approved the query. This can be useful for query administration.

This property is read-write.

ApproveDtTm

Description

This property returns the date-time stamp when the query was most recently approved. This can be useful for query administration.

This property is read-only.

CreateOprId

Description

Note. This property has been deprecated, and remains for backward compatibility only. Use the CreateUserId property instead.

This property returns a string containing the User Id of the user who created the query. This can be useful for query administration.

This property is read-only.

See Also

Query class: CreateUserId property.

CreateDtTm

Description

This property returns a string containing the date-time stamp indicating when the query was created. This property is set by the query runtime when a new query is saved. This can be useful for query administration.

This property is read-only.

CreateUserId

Description

This property returns a string containing the User Id of the user who created the query. This can be useful for query administration.

This property is read-only.

Description

Description

This property returns or sets the short description for the query.

The length of this property is 30 characters.

This property is read-write.

Disabled

Description

This property indicates if the query is active or not. This property returns a string value:

This property is read-write.

ExecAppName

Description

This property specifies the name of the application executing the query. This property isn't required. This means this property is used only if the query will be executed. This name is stored in the query execution log. This property returns a blank value when the query is opened.

When executing a query using the query API, this property should be set to the Application name that's executing it, so that the Query Monitor can track query execution. Doing this makes this property useful for query administration. If you try to read this property before setting it, you receive a NULL string.

This property should be set before using RunToRowset or RunToFile.

This property is read-write. However, this property is generally used only to set the name, rather than to read it.

ExecLogging

Description

This property indicates whether an execution log should be created when the query is executed. This can be useful for query administration. This property takes a Boolean value: True, create a log, False, don't create one.

The execution log, which is created by setting this property, can be viewed from the Query Monitor. The logging is done in a PeopleSoft Table. It stores, along with other relevant information, the Execution DateTime, Total Execution Time for the query, and Total Fetch Time for the query.

If you try to read this property before setting it, you receive a NULL string.

This property is read-write. However, this property is generally used only to set logging, rather than read.

Folder

Description

This property is used to group queries together. This parameter takes a string value, up to 18 characters.

This property is read-write.

LastSQLErrorCode

Description

This property returns the last SQL error code returned by the database as a number. The session object contains the error text and any other errors encountered while executing the query.

PeopleSoft recommends checking this value after using RunToRowset or RunToFile.

This property is read-only.

LastUpdDttm

Description

This property returns the most recent date-time when the query was updated as a string.

This property is read-only.

LastUpdOprId

Description

This property returns the User Id of the user who updated the query most recently as a string.

This property is read-only.

LongDescription

Description

This property returns or sets the long description for the query.

The length of this property is 256 characters.

This property is read-write.

Metadata

Description

This property returns a metadata collection.

This property is read-only.

Example

&MetadataList = &MyQuery.Metadata;

See Also

Query Metadata Collection.

MetaSQL

Description

This property returns the SQL that represents the query, unresolved for any platform.

For example, the MetaSQL property returns the following:

SELECT %DATEOUT(A.ASOF_DT)
 FROM PS_AEREQUESTTBL A
 WHERE A.ASOF_DT = %DATEIN('1900-01-01')

This property is read-only.

MoreRowsAvailable

Description

This property returns a Boolean value indicating whether “chunking” is turned on for the query.

This property is read-only.

Chunking Considerations

Every time a row is retrieved from the query result set, it is added to the internal output string managed by RunToString. At this point, the method can check to see if chunking is active. If so, and if the current size of the output string is larger than the ChunkSize parameter, then the query context is stored and the output string is returned. If not, then the next row is retrieved from the result set and the process continues.

When chunking is active, RunToString is intended to be called in a recursive fashion until the result set has been completely traversed. A Boolean property, MoreRowsAvailable, is included in the Query class to control recursive execution of these methods.

See Also

RunToString, .

Name

Description

This property returns the name of the query as a string.

The length of this property is 30 characters.

This property is read-only.

OutputUnicode

Description

Use this property to set or return a Boolean value indicating whether the RunToFile method will create a Unicode-encoded CSV file as output. This property is applicable only if the output format is set to %Query_TXT. Because the default value is false, OutputUnicode must be set before calling RunToFile.

Note. Because Microsoft Excel does not support UTF-8 encoding, the CSV file is written in binary mode with UCS-2 encoded data. Moreover, Excel does not automatically recognize Unicode-encoded, comma-delimited files even if they have a .csv extension. Therefore, the user receiving the file will not be able to open it by double-clicking. Instead, he or she must open it with Excel’s File, Open menu and choose the comma delimiter.

This property is read-write.

Example

&aQry.OutputUnicode = True;
&Result = &aQry.RunToFile(&rcdQryPrompts, &sOutFile, %Query_TXT, 0);

See Also

RunToFile

PDFFont

Description

This property is used to set the PDF Font number required for generating PDF using RunToFile. This property takes either a numeric or constant value. The values are:

If you try to read this property before setting it, you receive a NULL string.

This property is read-write.

See Also

Query class: RunToFile method, RunToRowset method.

Prompts

Description

This property returns all the prompts defined for the Query in a QueryPrompt Collection. If you just want the prompts used in the criteria, use the RunTimePrompts property.

This property is read-only.

See Also

RunTimePrompts, QueryPrompt Collection.

PromptRecord

Description

This property returns the runtime prompts of a query as an instance of a PeopleCode record object. This can be used with the Prompt built-in function to prompt for bind values. This record instance can also be used as the first input parameter for the RunToRowset and RunToFile methods.

This property is read-only.

See Also

Query class: RunToFile method, RunToRowset method.

PublicPrivate

Description

This property specifies whether the query is public or private.

You can use either a constant or numeric value for this property. The values are:

This property is read-write.

Example

If &QryObj.PublicPrivate = %Query_Public Then 
   /* code when working with Public Query */ 
Else 
   /* code when working with Private Query */ 
End-if;

QuerySelect

Description

This property returns the Main Select (that is, the first select) of the query definition as a QuerySelect object. For a new query which does not have any selects, it returns NULL.

The Query Records, Query Criteria, QueryOutput fields, and QuerySelected Fields can be obtained using the QuerySelect object only. Hence, after opening a query, this property serves as the starting point for getting the different components of the Select statement.

This property is read-only.

See Also

QuerySelect Class.

QueryStatistics

Description

This property returns statistical information pertaining to the query’s execution as a QueryStatistics object.

This property is read-only.

See Also

QueryStatistics Class.

RunTimePrompts

Description

This property returns the prompts used in the Query Definition’s criteria in a QueryPrompt Collection. To return all the prompts defined for the query, use the Prompts property.

This is property is read-only.

See Also

Prompts, QueryPrompt Collection.

SQL

Description

This property returns the SQL statement as generated from the query definition as a character string.

Note. The value of the SQL property is never stored as part of the query definition. Instead, it's generated by the system whenever it's needed for one of the RunTo methods or for the SQL property.

In the PeopleSoft Query, this is located under the SQL tab.

This property is read-only.

Considerations Using the SQL Property

This property returns a basic SQL statement. This statement does not include logic for doing related language record transactions or translate descriptions. PeopleSoft does not recommend using this SQL for selecting data. Instead, PeopleSoft recommends using the RunToRowset method for selecting data.

Type

Description

This property specifies the type of query. This parameter takes either a constant or number value. Values are:

This property is read-write.

QuerySelect Collection

A QuerySelect collection is returned from the QuerySelects property of each QuerySelect instance. It contains the unions and subqueries, which are children of the current select statement. PeopleSoft Query allows unions only for the main select. The QuerySelect instance for any subqueries cannot have any unions.

See QuerySelect class: QuerySelects property.

QuerySelect Collection Methods

In this section, we discuss the QuerySelect collection methods. The methods are discussed in alphabetical order.

AddUnion

Syntax

AddUnion()

Description

The AddUnion method adds a new QuerySelect object of type Union in the current QuerySelect collection. You can use AddUnion only with the first QuerySelect Collection because PeopleSoft Query doesn’t' support Unions for subqueries. All unions are considered children of the Main Select.

Parameters

None.

Returns

A reference to a QuerySelect object if successful. If the current QuerySelect Collection does not belong to the first Select statement, it returns NULL. If it fails, it returns NULL.

Example

&QryMainSel = &Query.QuerySelect; 
&QryMainSelCol = &QryMainSel.QuerySelects; 
&QryUnion = &QryMainSelCol.AddUnion();

DeleteQuerySelect

Syntax

DeleteQuerySelect(SelNumber)

Description

The DeleteQuerySelect method deletes the QuerySelect instance represented SelNumber.

Parameters

Returns

Returns 0 if successful.

Example

&QryMainSel = &Query.QuerySelect; 
&QryMainSelCol = &QryMainSel.QuerySelects; 
&QryMainSelCol.DeleteQuerySelect(3);

First

Syntax

First()

Description

The First method returns the first child QuerySelect object in the current QuerySelect’s collection.

Parameters

None.

Returns

A reference to a QuerySelect object if successful, NULL otherwise.

Example

&MyChildQrySel = &MySelCollection.First();

Item

Syntax

Item(number)

Description

The Item method returns the QuerySelect object that exists at the number position in the current QuerySelect collection.

Parameters

Returns

A reference to a QuerySelect object if successful, NULL otherwise.

Example

For &I = 1 to &QrySelCol.Count; 
   &MyChildQrySel = &QrySelCol.Item(&I); 
   /* do processing */ 
End-For;

ItemBySelNum

Syntax

ItemBySelNum(SelNumber)

Description

The ItemBySelNum method returns the QuerySelect object specified by the Select Number. Each Select Statement in a Query Definition is identified by a unique select number.

Parameters

Returns

A reference to a QuerySelect object if successful, NULL otherwise.

Example

&MyChildQuerySelect = &MySelCol.ItemBySelNum(3);

Next

Syntax

Next()

Description

The Next method returns the next QuerySelect object in the current QuerySelect collection. You can use this method only after you have used the First method: otherwise the system returns a NULL.

Parameters

None.

Returns

A reference to a QuerySelect object if successful, NULL otherwise.

Example

&MyChildQuerySelect = &MySelCol.Next();

QuerySelect Collection Property

In this section, we discuss the Count property.

Count

Description

This property returns the total number of QuerySelect objects in the current QuerySelect Collection, as a number.

This property is read-only.

Example

&COUNTCHILDSELS = &MYSELCOL.Count;

QuerySelect Class

The QuerySelect class represents the select statement of the SQL used in the query definition. This can be any of the following:

• The main select statement.

• The select statement in each union.

• The select statement in each subquery.

Consider the following SQL statement:

select ACCOUNT_NUM from GL_ACCOUNT_TBL_00

where PRODUCT_ID in (select DISTINCT PRODUCT_ID FROM ORDER_TBL)

union

select ACCOUNT_NUM from GL_ACCOUNT_TBL_01

In this example, there are three select statements. The first select statement is the main statement. The second select statement is a subquery, which is then used in the criterion for the first select statement. The union contains the third select statement. There are records and fields associated with each select statement. In the query API, the first select is accessible using Query.QuerySelect, which can be obtained as shown:

&MainSelect = &Qry.QuerySelect;

The union can be obtained from the main select, as shown:

/* There can be more than one union, hence there is collection of selects */
/* in the main select */

&Union1 = &MainSelect.QuerySelects.Item(1);

The subquery is obtained from the main select as shown:

/* belongs to first criteria of the select */

&SubQry1 = &MainSelect.Criteria.Item(1).Expr2SubQuery; 

Therefore, there is one QuerySelect instance for the main select of the query, one instance per union defined in the query, and one instance per subquery.

In PeopleSoft Query, the first select (that is, the main select) is the parent of all the unions and subqueries. The main select is obtained with the QuerySelect property. The unions and subqueries are obtained from the QuerySelects property of each QuerySelect instance.

A QuerySelect object is returned from the following:

• The QuerySelect collection methods AddUnion, First, Item, ItemBySelNum, or Next

• The QuerySelect Query property

See QuerySelect Collection, QuerySelects.

QuerySelect Methods

In this section, we discuss each QuerySelect method. The methods are arranged in alphabetical order.

AddAllFields

Syntax

AddAllFields(QueryRecord)

Description

The AddAllFields method adds all the fields in the specified query record to the query definition, that is, makes them all QueryOutputFields. The query record must already be a part of the query definition.

Parameters

Returns

An integer: 0 if successfully added.

See Also

QuerySelect class: AddQueryOutputField method, AddQueryRecord method, AddQuerySelectedField method.

AddCriteria

Syntax

AddCriteria(Name)

Description

The AddCriteria method adds new criterion to the query definition. This method returns a reference to a new QueryCriteria object that you can then use to specify details about the criteria.

Note. If you do not specify a unique name for Name when adding a criteria, a criteria object referencing the new criteria is returned, not the existing criteria object.

Parameters

Returns

A reference to a QueryCriteria object.

See Also

QuerySelect class: DeleteCriteria method, Criteria property, QueryCriteria Class .

AddExpression

Syntax

AddExpression(Name)

Description

The AddExpression method adds an expression to the query definition. It returns a reference to a new QueryExpression object you can use to specify details about the expression.

Parameters

Returns

A reference to a QueryExpression object.

See Also

FindExpression, DeleteExpression, Expressions, QueryExpression Class.

AddHavingCriteria

Syntax

AddHavingCriteria(Name)

Description

The AddHavingCriteria method adds new criterion to the query definition, which is intended for use in the HAVING clause of the SELECT statement. This method returns a reference to a new QueryCriteria object that you can then use to specify details about the having criteria.

Note. If you do not specify a unique name for Name when adding a criterion, a criteria object referencing the new criteria is returned, not the existing criteria object.

Parameters

Returns

A reference to a QueryCriteria object.

See Also

QuerySelect class: DeleteHavingCriteria method, HavingCriteria property, QueryCriteria Class .

AddQueryOutputField

Syntax

AddQueryOutputField(QueryRecord, index)

Description

The AddQueryOutputField method adds a query output field to the query definition. This method returns a reference to a new QueryOutputField object that you can then use to specify attributes for the query definition.

Parameters

Returns

A reference to a QueryOutputField object.

See Also

AddAllFields, QueryField Class.

AddQueryRecord

Syntax

AddQueryRecord(QueryRecordName)

Description

The AddQueryRecord method adds a query record to the query definition. You must specify a valid record name in the string parameter QueryRecordName. This method returns a reference to the record as a QueryRecord.

Parameters

Returns

A reference to the record as a QueryRecord object.

See Also

QueryRecord Class.

AddQuerySelectedField

Syntax

AddQuerySelectedField(QueryRecordName, RecordAlias, FieldName, Heading)

Description

The AddQuerySelectedField method adds a query field to the query definition, as a QuerySelectedField. This method returns a reference to a new QuerySelectedField object that you can then use to specify attributes for the query definition. At the time it's added, it isn't an output field. This can be changed by setting the column number of the field to a value greater than zero.

Parameters

Returns

A reference to a QuerySelectedField object.

See Also

QuerySelect class: AddAllFields method, DeleteField method, QueryField Class .

DeleteCriteria

Syntax

DeleteCriteria(index)

Description

The DeleteCriteria method deletes the criteria specified by index from the query definition.

Parameters

Returns

An integer: 0 if successfully deleted.

See Also

QuerySelect class: AddCriteria method, Criteria property.

DeleteExpression

Syntax

DeleteExpression(index)

Description

The DeleteExpression method deletes the specified expression from the query definition.

Parameters

Returns

An integer: 0 if successfully deleted.

See Also

QuerySelect class: AddExpression method, Expressions property, QueryExpression Class .

DeleteField

Syntax

DeleteField(index)

Description

The DeleteField method deletes the selected field from the query definition. This does not delete the field from the QueryRecord, just from the query definition, so it's no longer selected.

Parameters

Returns

An integer: 0 if successfully deleted.

See Also

AddAllFields, QueryField Class.

DeleteHavingCriteria

Syntax

DeleteHavingCriteria(index)

Description

The DeleteHavingCriteria method deletes the having criteria specified by index from the query definition.

Parameters

Returns

An integer value: 0 means the having criteria was deleted successfully.

See Also

QuerySelect class: AddHavingCriteria method, HavingCriteria property.

DeleteRecord

Syntax

DeleteRecord(index)

Description

The DeleteRecord method deletes the selected record from the query definition. This does not delete the record from the database or the QueryDBRecord collection, just from the query definition, so it's no longer selected.

Parameters

Returns

An integer: 0 if successfully deleted.

See Also

AddQueryRecord, QueryField Class.

QuerySelect Properties

In this section, we discuss the QuerySelect properties. The properties are discussed in alphabetical order.

Criteria

Description

This property returns a reference to a QueryCriteria Collection for the simple criteria of the SELECT (that is, the criteria that are used in the where clause of the select statement.)

This property is read-only.

See Also

QueryCriteria Collection.

Distinct

Description

This property specifies if the Select is distinct or not.

This property takes a Boolean value: True if the query is distinct, False if the query isn't distinct.

This property is read-write.

Expressions

Description

This property returns a reference to a QueryExpression Collection.

This property is read-only.

See Also

QueryExpression Collection.

HavingCriteria

Description

This property returns a reference to a QueryCriteria collection populated with Having criteria.

This property is read-only.

See Also

QueryCriteria Collection.

ParentSelectNum

Description

This property returns the select number of the parent select of the current select object. Every QuerySelect is assigned a unique number indicating its place in the hierarchy of select statements. For the Main Select, this number is zero. For unions, this number is 1 (unions aren't allowed in subqueries.)

This property is read-only.

QueryOutputFields

Description

This property returns a reference to a QueryField Collection made up of QueryOutputFields.

The collection of QueryOutputFields does not necessarily include all columns returned in query resultset. This collection only includes columns that have been added to query output collection using the query classes or by an end-user designing a query.

PeopleSoft Query can also add additional columns for related language processing and also for translate labels on fields, and so you cannot use the Query Output Collections as a way to discover all of the columns that are returned in the resultset or by executing the SQL generated from a query.

This is property is read-only.

See Also

QueryField Collection.

QueryRecords

Description

This property returns a reference to a QueryRecord Collection for the query.

This is property is read-only.

See Also

QueryRecord Collection.

QuerySelectedFields

Description

This property returns a reference to a QueryField Collection made up of QuerySelectedFields.

This is property is read-only.

See Also

QueryField Collection.

QuerySelects

Description

This property returns all the QuerySelect objects that are children of the current select statement as a QuerySelect collection. If the query contains unions, this property contains unions and subqueries (if any criteria contain subqueries). For all other selects, this property contains only the subqueries (because PeopleSoft Query doesn't allow unions for sub-queries).

This is property is read-only.

See Also

QuerySelect Collection.

SelectNum

Description

This property returns the select number of the current select. Every QuerySelect is assigned a unique number indicating its place in the hierarchy of select statements. For the Main Select, this number would be 1.

This property is read-only.

SelectType

Description

This property specifies what type of select statement. You can check for either a numeric or a constant value. The values are:

This property is read-only.

QueryRecord Collection

A QueryRecord collection is returned from the QueryRecords QuerySelect class method.

See QuerySelect class: QueryRecords property.

QueryRecord Collection Methods

In this section, we discuss each QueryRecord collection method. The methods are discussed in alphabetical order.

First

Syntax

First()

Description

The First method returns the first QueryRecord object in the QueryRecord collection.

Parameters

None.

Returns

A reference to a QueryRecord object if successful, NULL otherwise.

Example

&MyQueryRecord = &MyCollection.First();

Item

Syntax

Item(number)

Description

The Item method returns the QueryRecord object that exists at the number position in the QueryRecord collection.

Parameters

Returns

A reference to a QueryRecord object if successful, NULL otherwise.

Example

For &I = 1 to &QueryRecordColl.Count; 
   &MyQueryRecord = &QueryRecordColl.Item(&I); 
   /* do processing */ 
End-For;

ItemByAlias

Syntax

ItemByAlias(Alias)

Description

The ItemByAlias method returns the QueryRecord object specified by Alias.

Parameters

Returns

A reference to a QueryRecord object if successful, NULL otherwise.

Example

&MyQueryRecord = &MyCollection.ItemByAlias("A");

Next

Syntax

Next()

Description

The Next method returns the next QueryRecord object in the QueryRecord collection. You can use this method only after you have used the First method: otherwise the system doesn’t know where to start.

Parameters

None.

Returns

A reference to a QueryRecord object if successful, NULL otherwise.

Example

&MyQueryRecord = &MyCollection.Next();

QueryRecord Collection Property

In this section, we discuss the Count property.

Count

Description

This property returns the number of QueryRecord objects in the QueryRecord Collection, as a number.

This property is read-only.

Example

&COUNT = &MY_COLLECTION.Count;

QueryRecord Class

A QueryRecord object is returned from the following:

• The QueryRecord Collection methods First, Item, ItemByName, or Next.

• The AddQueryRecord QuerySelect class method.

• The QueryRecord QueryField class method.

See QueryRecord Collection, AddQueryRecord, QueryRecord.

QueryRecord Class Method

In this section, we discuss the GetField method.

GetField

Syntax

GetField(Index)

Description

The GetField method returns the QueryField object specified by index from the QueryRecord collection.

Parameters

Returns

A reference to a QueryField object if successful, NULL otherwise.

Example

&QryFieldColl = &QryRec.QueryFields; 
 
For &I = 1 to &QryFieldColl.Count 
   &QryField = &QryRec.GetField(&I); 
   /* do processing */ 
End-For;

QueryRecord Class Properties

In this section, we discuss each QueryRecord class property. The properties are discussed in alphabetical order.

Description

Description

This property returns the short description of the record as a string.

The length of this property is 30 characters.

This property is read-only.

JoinAlias

Description

This property returns or sets the join record’s alias, that is, A, B, C, and so on.

This is applicable in any type of join. The value is a string. The length of this property is 1 character.

This property is read-write.

JoinFieldName

Description

This property returns or sets the join record’s field used in the join criteria. This is applicable in lookup-table joins. The value is a string.

The length of this property is 18 characters.

This property is read-write.

JoinType

Description

This property returns or sets the join type. This is applicable in any type of join. You can use either a constant or numeric value.

The values for the join type are:

This property is read-write.

Name

Description

This property returns the name of the record as a string.

The length of this property is 15 characters.

This property is read-only.

QueryFields

Description

This property returns a reference to a QueryField Collection that contains instances of all the fields belonging to the record definition.

This property is read-only.

See Also

QueryField Collection.

RecordAlias

Description

This property returns the alias used for the record in the query (that is, A, B, C, and so on.)

The length of this property is 1 character.

This property is read-write.

QueryField Collection

A QueryField collection is returned from the following:

• The QueryOutputFields of QuerySelect class method.

• The QuerySelectedFields of QuerySelect class method.

• The QueryFields QueryRecord class method.

The collection of QueryOutputFields does not necessarily include all columns returned in query resultset. This collection only includes columns that have been added to query output collection using the query classes or by an end-user designing a query.

PeopleSoft Query can also add additional columns for related language processing and also for translate labels on fields, and so you cannot use the Query Output Collections as a way to discover all of the columns that are returned in the resultset or by executing the SQL generated from a query.

See QueryOutputFields, QuerySelectedFields, QueryFields.

QueryField Collection Methods

In this section, we discuss each QueryField method. The methods are discussed in alphabetical order.

First

Syntax

First()

Description

The First method returns the first QueryField object in the QueryField collection.

Parameters

None.

Returns

A reference to a QueryField object if successful, NULL otherwise.

Example

&MyQueryField = &MyCollection.First();

Item

Syntax

Item(number)

Description

The Item method returns the QueryField object that exists at the number position in the QueryField collection.

Parameters

Returns

A reference to a QueryField object if successful, NULL otherwise.

Example

For &I = 1 to &QueryFieldColl.Count; 
   &MyQueryField = &QueryFieldColl.Item(&I); 
   /* do processing */ 
End-For;

ItemByNameAndAlias

Syntax

ItemByNameAndAlias(Name, RecordAlias)

Description

The ItemByNameAndAlias method returns the QueryField object with the given Name and Record Alias.

Parameters

Returns

A reference to a QueryField object if successful, NULL otherwise.

Example

&MyQueryField = &MyCollection.ItemByNameAndAlias("PHONELIST", "A");

ItemByExpNum

Syntax

ItemByExpNum(ExpNum)

Description

The ItemByExpNum method returns the QueryField object with the given Expression Number, ExpNum.

Parameters

Returns

A reference to a QueryField object if successful, NULL otherwise.

Example

&QryFld = &QryFldCol.ItemByExpNum(1);

Next

Syntax

Next()

Description

The Next method returns the next QueryField object in the QueryField collection. You can use this method only after you have used the First method: otherwise the system doesn’t know where to start.

Parameters

None.

Returns

A reference to a QueryField object if successful, NULL otherwise.

Example

&MyQueryField = &MyCollection.Next();

QueryField Collection Property

In this section, we discuss the Count property.

Count

Description

This property returns the number of QueryField objects in the QueryField Collection, as a number.

This property is read-only.

Example

&COUNT = &MY_COLLECTION.Count;

QueryField Class

A QueryField object is returned by the following:

• The AddQuerySelectedField of QuerySelect class method.

• The AddQueryOutputField of QuerySelect class method.

• The QueryField collection methods First, Item, ItemByName or Next.

See QuerySelect class: AddQuerySelectedField method, AddQueryOutputField method.

See QueryField Collection.

QueryField Class Methods

In this section, we discuss each QueryField method. The methods are discussed in alphabetical order.

AddTranslateExpression

Syntax

AddTranslateExpression(ExpressionName)

Description

Use the AddTranslateExpression method to add Translate Effective Date Logic expressions for translatable fields.

Parameters

Returns

A reference to a QueryExpression object.

See Also

QuerySelect class: Expressions property, DeleteExpression method, AddExpression method, AddTranslateField method, QueryExpression Class .

AddTranslateField

Syntax

AddTranslateField(FieldName)

Description

Use the AddTranslateField method to add a Translate Effective Date Logic field for a translatable field.

Parameters

Returns

A reference to a QueryField object.

See Also

QuerySelect class: Expressions property, DeleteExpression method, AddExpression method, AddTranslateExpression method, QueryExpression Class .

GetImageFormat

Syntax

GetImageFormat()

Description

Use the GetImageFormat method to differentiate between images and files, both stored as binary large objects (BLOBs) in the database.

Note. Because images and files share the field type value 8, this method is required to differentiate between the two types.

Parameters

None.

Returns

A number. A value 16 indicates that the field is of type file (or attachment).

See Also

Type

QueryField Class Properties

In this section, we discuss the QueryField class properties. The properties are discussed in alphabetical order.

Aggregate

Description

This property returns or sets the aggregate type for the query field.

This property takes either a constant or numeric value. The values are:

This property is read-write.

ColumnNumber

Description

This property returns or sets the column number for the query field as a number. If the column number is greater than zero, the field is an output field.

This property is read-write.

Description

Description

This property returns the long description of the field as a string. This same value is returned by the Description property of the QueryDBRecordField class.

The length of this property is 30 characters.

This property is read-only.

Decimal

Description

This property returns the decimal positions QueryField as a number. This same value is returned by the Decimal property of the QueryDBRecordField class.

This property is read-only.

ExpNum

Description

This property returns or sets the Expression Number for the field. This value is 0 for non-expression fields. If the field is created for an expression, this value is the same as the expression number of the corresponding expression. This property takes a number value.

This property is read-write.

Flag

Description

This property returns the Use Edit flag for the Query Field. It has the same values as returned by the Flag property of the QueryDBRecordField. This property takes a number value.

This property is read-only.

Format

Description

This property returns the field format for the query field. This property returns a string value. This property is useful for displaying the data type in a string format.

Values are:

This property is read-only.

HeadingText

Description

This property returns or sets the heading text for the query field as a string.

The length of this property is 30 characters.

This property is read-write.

HeadingType

Description

This property returns or sets the heading type for the query field. This property takes either a numeric or constant value. The values are:

This property is read-write.

HeadingUniqueFieldName

Description

This property returns or sets the unique field name for the heading. The default value for this property is the record alias combined with the field name.

The length of this property is 30 characters.

This property is read-write.

Length

Description

This property returns the length of the Query Field. The same value is returned by the Length property of the QueryDBRecordField. This property takes a numeric value.

This property is read-only.

LongName

Description

This property returns the long name of the Query Field. The same value is returned by the Long Name property of the QueryDBRecordField. This property takes a string value.

The length of this property is 30 characters.

This property is read-only.

Name

Description

This property returns the name of the query field as a string.

The length of this property is 18 characters.

This property is read-only.

OrderByDirection

Description

This property returns or sets the order by direction. This property takes a numeric value. The constants are the ASCII codes for space (" ") and "D".

Values are:

This property is read-write.

Example

The following sets the order by direction to be ascending.

&QueryField.OrderByDirection = Code(" ");

The following sets the order by direction to be descending.

&QueryField.OrderByDirection = Code("D");

OrderByNumber

Description

Use this property to specify whether a field is used as part of an 'Order By' statement in the SQL. The number value of this property indicates which is the first field in the order by statement, which is the second, and so on.

This property is read-write.

Example

To order a query by one field, you must set the OrderByNumber property for the other QueryFields as well, specifying the primary field as 1, the next field as 2, and so on.

&QryFld = &MainQrySel.AddQuerySelectedField("PSRECDEFN", "A", "RECNAME", ⇒
"Record Name"); 
&QryFld.ColumnNumber = 1; 
​&QryFld.OrderByNumber = 1; 
​ 
&QryFld = &MainQrySel.AddQuerySelectedField("PSRECDEFN", "A", "RECDESCR", ⇒
"Record Descr"); 
&QryFld.ColumnNumber = 2; 
​&QryFld.OrderByNumber = 2; 
​ 
&QryFld = &MainQrySel.AddQuerySelectedField("PSRECDEFN", "A", "RELLANGRECNAME",⇒
 "Record Lang Rec"); 
&QryFld.ColumnNumber = 3; 
​&QryFld.OrderByNumber = 3;

QueryRecord

Description

This property returns a reference to the QueryRecord containing this QueryField. If the field has no QueryRecord (that is, that this field is an expression field), this property returns NULL.

This property is read-only.

Example

&QryRcd = &QryFld.QueryRecord;

RecordAlias

Description

This property returns the record alias for the Query Field as a string. This value is usually a value like "A", "B", and so on. The same value is returned by the RecordAlias property for QueryRecord.

The length of this property is 1 character.

This property is read-only.

ShortName

Description

This property returns the short name of the Query Field. The same value is returned by the ShortName property for QueryDBRecordField. This property takes a string value.

The length of this property is 15 characters.

This property is read-only.

TranslateEffDtLogic

Description

This property returns or sets the effective date logic for the QueryField.

The values are:

This property is read-write.

TranslateExpression

Description

This property returns a reference to an expression object based on a translate field if the Translate Effective Date option refers to an Expression

This property is read-only.

See Also

QueryExpression Class.

TranslateField

Description

This property returns a reference to a QueryField object for a translate field if the Translate Effective Date Option refers to a field.

This property is read-only.

See Also

QueryField Class.

TranslateOption

Description

This property returns or sets the translate value for the QueryField. This property takes a numeric or constant value. The values are:

This property is read-write.

Type

Description

This property returns the type of the field. You can use either a numeric or constant value. The values are:

Note. The Image and File types can be differentiated using the GetImageFormat method of the QueryField class.

This property is read-only.

See Also

GetImageFormat

QueryCriteria Collection

A QueryCriteria collection is returned from the Criteria QuerySelect class property.

See QuerySelect class: Criteria property.

QueryCriteria Collection Methods

In this section, we discuss the QueryCriteria collection methods. The methods are discussed in alphabetical order.

First

Syntax

First()

Description

The First method returns the first QueryCriteria object in the QueryCriteria collection.

Parameters

None.

Returns

A reference to a QueryCriteria object if successful, NULL otherwise.

Example

&MyQueryCriteria = &MyCollection.First();

Item

Syntax

Item(number)

Description

The Item method returns the QueryCriteria object that exists at the number position in the QueryCriteria collection.

Parameters

Returns

A reference to a QueryCriteria object if successful, NULL otherwise.

Example

For &I = 1 to &MyCollection.Count; 
   &MyQueryCriteria = &MyCollection.Item(&I); 
   /* do processing */ 
End-For;

ItemByName

Syntax

ItemByName(CriteriaName)

Description

The ItemByName method returns the QueryCriteria object that exists with the passed CriteriaName in the QueryCriteria collection.

Parameters

Returns

A reference to a QueryCriteria object if successful, NULL otherwise.

Example

&MyQueryCriteria = &MyCollection.ItemByName("PHONELIST");

Next

Syntax

Next()

Description

The Next method returns the next QueryCriteria object in the QueryCriteria collection. You can use this method only after you have used the First method: otherwise the system doesn’t know where to start.

Parameters

None.

Returns

A reference to a QueryCriteria object if successful, NULL otherwise.

Example

&MyQueryCriteria = &MyCollection.Next();

QueryCriteria Collection Property

In this section, we discuss the Count property.

Count

Description

This property returns the number of QueryCriteria objects in the QueryCriteria Collection, as a number.

This property is read-only.

Example

&COUNT = &MY_COLLECTION.Count;

QueryCriteria Class

A QueryCriteria object is returned from the following:

• The AddCriteria and AddHavingCriteria QuerySelect class method.

• The First, Item, and Next method of the QueryCriteria Collection.

See QuerySelect class: AddCriteria method, AddHavingCriteria method.

See QueryCriteria Collection.

See Working With Query Criteria and Expressions.

See Defining Selection Criteria.

QueryCriteria Class Methods

In this section, we discuss the QueryCriteria class methods. The methods are discussed in alphabetical order.

AddExpr1Expression

Syntax

AddExpr1Expression()

Description

The AddExpr1Expression method returns a reference to a new a QueryExpression object to be used as an expression for Expression 1. You can then use this object to specify details about the expression using the methods and properties of the QueryExpression Class.

Note. You must set the type for Expression 1 using the Expr1Type property before you can use this method.

Parameters

None.

Returns

A reference to a blank QueryExpression object.

See Also

QueryCriteria class: Expr1Expression property, AddExpr1Field method, Expr1Type property, QueryExpression Class .

AddExpr1Field

Syntax

AddExpr1Field(QueryRecordAlias, FieldName)

Description

The AddExpr1Field method returns a reference to a new a QueryField object to be used as a field for Expression 1. You can then use this object to specify details about the expression using the methods and properties of the QueryField Class.

Note. You must set the type for Expression 1 using the Expr1Type property before you can use this method.

Parameters

Returns

A reference to a blank QueryField object.

See Also

QueryCriteria class: Expr1Field property, AddExpr1Expression method, Expr1Type property, QueryField Class .

AddExpr2Expression

Syntax

AddExpr2Expression()

Description

The AddExpr2Expression method returns a reference to a new a QueryExpression object to be used as an expression for Expression 2. You can then use this object to specify details about the expression using the methods and properties of the QueryExpression Class.

Parameters

None.

Returns

A reference to a blank QueryExpression object.

See Also

QueryExpression Class.

AddExpr2Field1

Syntax

AddExpr2Field1(QueryRecordAlias, FieldName)

Description

Expression 2 has two field expressions because when the Between relational operator is used in a criteria, there can be two expression fields. The AddExpr2Field1 method returns a reference to a new QueryField object to be used as a field 1 for Expression 2. You can then use this object to specify details about the expression using the methods and properties of the QueryField Class.

Parameters

Returns

A reference to a blank QueryField object.

See Also

QueryRecord Class, QueryField Class.

AddExpr2Field2

Syntax

AddExpr2Field2(QueryRecordAlias, FieldName)

Description

Expression 2 has two field expressions because when the Between relational operator is used in a criteria, there can be two expression fields. The AddExpr2Field2 method returns a reference to a new a QueryField object to be used as a field 2 for Expression 2. You can then use this object to specify details about the expression using the methods and properties of the QueryField Class.

Parameters

Returns

A reference to a blank QueryField object.

See Also

QueryRecord Class, QueryField Class.

AddExpr2List

Syntax

AddExpr2List()

Description

Expression 2 can have a list of values when the Operator is of type In List (or Not In List). The AddExpr2List method returns a reference to a new QueryList, which can be used to add a list of values to the criteria.

Parameters

None.

Returns

A reference to a blank QueryList object.

See Also

QueryList Class.

AddExpr2Subquery

Syntax

AddExpr2Subquery()

Description

The AddExpr2Subquery method is used to create a subquery for Expression2. This method returns a new QuerySelect object you can use to specify details about the new subquery.

Warning! The new subquery created with this method replaces any existing subquery (for this criteria), destroying any existing properties or values.

Parameters

None.

Returns

A reference to a new QuerySelect object.

See Also

QuerySelect Class.

QueryCriteria Class Properties

In this section, we discuss the QueryCriteria class properties. The properties are discussed in alphabetical order.

Expr1Expression

Description

This property returns a reference to the QueryExpression object that's used as Expression 1.

This property is valid only when Expression 1 exists as an expression. If you want to add an expression for Expression 1, use the AddExpr1Expression method.

This property is read-write.

See Also

QueryCriteria class: AddExpr1Expression method.

Expr1Field

Description

This property returns a reference to the QueryField object that's used as Expression 1.

This property is valid only when Expression 1 exists as a field. You can then use the QueryField Class methods and property to manipulate this object.

If you want to add a field for Expression 1, use the AddExpr1Field method.

This property is read-only.

See Also

QueryField Class, AddExpr1Field.

Expr1Type

Description

This property returns or sets the type for Expression 1.

Note. You must set the type of expression for every new criteria.

The values are:

This property is read-write.

Example

The following is used to test the expression to determine the property to use to retrieve it:

&MyExpr1 = &MyCrtColl.Next(); 
If &MyExpr1.Expr1Type = %Query_ExprField Then /* Expression is a Field */ 
 
   &OldExpr1Value = &MyExpr1.Expr1Field; 
   /* do processing */ 
 
Else  /* Expression 1 is an expression */ 
 
   &OldExpr1Value = &MyExpr1.Expr1Expression; 
   /* Do processing */ 
 
End-if;

The following is an example showing how to add a field for Expression 1.

/* add a new criteria */ 
&MyCriteria = &MyQuery.AddCriteria(); 
 
/* set the type of the first expression to be a field */ 
​&MyCriteria.Expr1Type = %Query_ExprField; 
 
​/* add the field EMPLID from the ABSENCE_HIST record */ 
&MyField = &MyCriteria.AddExpr1Field("A", "EMPLID");

Expr2Constant1

Description

If the Between relational operator is used in the criteria, there can be two constants for Expression 2. This property returns or sets the constant value for the first constant for Expression 2. This property takes a string value.

This property is valid only when Expression 2 is defined as a constant.

This property is read-write.

Expr2Constant2

Description

If the Between relational operator is used in the criteria, there can be two constants for Expression 2. This property returns or sets the constant value for the second constant for Expression 2. This property takes a string value.

This property is valid only when Expression 2 is defined as a constant.

This property is read-write.

Expr2Expression1

Description

If the Between relational operator is used in the criteria, there can be two expressions for Expression 2. This property returns a reference to the first QueryExpression object that's used as Expression 2.

This property is valid only when Expression 2 exists as an expression. To add an expression for Expression 2, use the AddExpr2Expression method.

This property is read-write.

See Also

QueryCriteria class: AddExpr2Expression method.

Expr2Expression2

Description

If the Between relational operator is used in the criteria, there can be two expressions for Expression 2. This property returns a reference to the second QueryExpression object that's used as Expression 2.

This property is valid only when Expression 2 exists as an expression. To add an expression for Expression 2, use the AddExpr2Expression method.

This property is read-write.

See Also

QueryCriteria class: AddExpr2Expression method.

Expr2Field1

Description

If the Between relational operator is used in the criteria, there can be two fields for Expression 2. This property returns a reference to the first QueryField object that's used as Expression 2.

This property is only valid when Expression 2 exists as a field. You can then use the QueryField Class methods and property to manipulate this object.

To add a field for Expression 2, use the AddExpr2Field1 method.

This property is read-only.

See Also

AddExpr2Field1, QueryField Class.

Expr2Field2

Description

If the Between relational operator is used in the criteria, there can be two fields for Expression 2. This property returns a reference to the second QueryField object that's used as Expression 2.

This property is valid only when Expression 2 exists as a field. You can then use the QueryField Class methods and property to manipulate this object.

To add a field for Expression 2, use the AddExpr2Field2 method.

This property is read-only.

See Also

AddExpr2Field2, QueryField Class.

Expr2List

Description

This property returns a reference to the QueryList object of Expression 2 that's used when the Operator is of type In List (or Not In List).

This property is read-only.

Expr2Subquery

Description

This property returns a reference to the QuerySelect object that's used as a subquery for Expression 2.

This property is valid only when Expression 2 exists as a subquery. To add a subquery for Expression 2, use the AddExpr2Subquery method.

This property is read-only.

See Also

QueryCriteria class: AddExpr2Subquery method.

Expr2Type

Description

This property returns or sets the type for Expression 2. The following table lists all of possible values for this property. However, the values for this property are dependent upon the Operator property.

This property is read-write.

See Operator, Working With Query Criteria and Expressions.

You can use either a constant or numeric value for this property. The values are:

The following table describes how to access or change Expression 2 depending on the Expression2 Type.

Example

The following is used to test the expression to determine the property to use to retrieve it:

&MyExpr2 = &MyCriteria.Expr2Expression; 
 
If &MyExpr2.Expr2Type = %Query_ExprConstant Then /* Expression is a constant */ 
 
   &OldValue = &MyExpr2.Expr2Constant; 
   /* do processing */ 
 
End-if;

The following is an example showing how to add a field for Expression 2:

( )
/* After setting the first expression, set the operator for the criteria*/
​&MyCriteria.Operator = %Query_CondEqual;

/* Set the type of the second expression to be a field */
​&MyCriteria.Expr2Type = %Query_ExprField;

/* Add the EMPLID field from the ABSENCE_HIST record whose record alias is A */
&MyField = &MyCriteria.AddExpr2Field("A", "EMPLID" );

Logical

Description

This property returns or sets the logical portion of a criteria.

Note. This property is valid only when there are more than one criteria for a query. Also, this property is required when there is more than one criteria for a query.

The values are:

This property is read-write.

LParenLvl

Description

This property returns or sets the left parenthesis level used for grouping criteria. This property takes a numeric value.

This property is read-write.

Name

Description

This property returns the name of the Query Criteria, as a string.

This property is read-only.

Negation

Description

This property returns a Boolean value, indicating whether a criterion is negated: True if the criterion is negated, False if it isn't.

This property is read-write.

Operator

Description

This property returns or sets the operator for the criteria.

The value of this property determines the valid types of the Expression 2, set with the Expr2Type property.

This property is read-write.

See Expr2Type, Working With Query Criteria and Expressions.

You can use either a constant or numeric value for this property. The values are:

R1ExprNum

Description

This property returns or sets the expression number for the first expression of Expression 2. This property takes a numeric value.

This property is read-write.

R2ExprNum

Description

This property returns or sets the expression number for the second expression of Expression 2. This property takes a numeric value.

This property is read-write.

R1ExprType

Description

This property returns the expression type for the first expression of Expression 2. This property takes a numeric value and is the same range of values as the Expr2Type. It helps distinguish the type of an expression based on the value of Expr2Type. For instance, if the Expr2Type is Field-Expr, R1Expr2Type is of type Field and R2Expr2Type is of type Expression.

This property is read-only.

R2ExprType

Description

This property returns the expression type for the second expression of Expression 2. This property takes a numeric value and is the same range of values as the Expr2Type. It helps distinguish the type of an expression based on the value of Expr2Type. For instance, if the Expr2Type is Field-Expr, R1Expr2Type is of type Field and R2Expr2Type is of type Expression.

This property is read-only.

RParenLvl

Description

This property returns or sets the right parenthesis level used for grouping criteria. This property takes a numeric value.

This property is read-write.

QueryExpression Collection

A QueryExpression Collection is returned from the Expressions Query class property.

See QuerySelect class: Expressions property.

QueryExpression Collection Methods

In this section, we discuss the QueryExpression collection methods. The methods are described in alphabetical order.

First

Syntax

First()

Description

The First method returns the first QueryExpression object in the QueryExpression collection.

Parameters

None.

Returns

A reference to a QueryExpression object if successful, NULL otherwise.

Example

&MyQueryExpression = &MyCollection.First();

Item

Syntax

Item(number)

Description

The Item method returns the QueryExpression object that exists at the number position in the QueryExpression collection.

Parameters

Returns

A reference to a QueryExpression object if successful, NULL otherwise.

Example

For &I = 1 to &QueryExpressionColl.Count; 
   &MyQueryExpression = &QueryExpressionColl.Item(&I); 
   /* do processing */ 
End-For;

ItemByName

Syntax

ItemByName(ExpressionName)

Description

The ItemByName method returns the QueryExpression object in the QueryExpressionCollection with the given expression name.

Parameters

Returns

A reference to a QueryExpression object if successful, NULL otherwise.

Example

&QryExpr = &QryExprCol.Item("Exp-6");

Next

Syntax

Next()

Description

The Next method returns the next QueryExpression object in the QueryExpression collection. You can use this method only after you have used the First method: otherwise the system doesn’t know where to start.

Parameters

None.

Returns

A reference to a QueryExpression object if successful, NULL otherwise.

Example

&MyQueryExpression = &MyCollection.Next();

QueryExpression Collection Property

In this section, we discuss the Count property.

Count

Description

This property returns the number of QueryExpression objects in the QueryExpression Collection, as a number.

This property is read-only.

Example

&COUNT = &MY_COLLECTION.Count;

QueryExpression Class

A QueryExpression object is returned by the following:

• The AddExpression method of the Query class.

• The First, Item and Next methods of the QueryExpression collection.

• Some of the QueryCriteria class methods.

• The Expr1Expression and Expr2Expression1 properties of the QueryCriteria class.

See AddExpression, QueryExpression Collection, QueryCriteria Class Methods, Expr1Expression, Expr2Expression1.

See Working With Query Criteria and Expressions.

QueryExpression Class Properties

In this section, we discuss the QueryExpression class properties. The properties are discussed in alphabetical order.

Aggregate

Description

This property specifies whether the expression is an aggregate function.

This property takes a Boolean value: True if the expression is an aggregate function, False, otherwise.

This property is read-write.

BindFlag

Description

This property specifies whether the expression contains a bind value, as Boolean value.

This property is mainly used while reading a query, to determine whether a Query Expression contains a bind value. It isn't necessary to set this value while saving a query to the database.

Values are:

This property is read-write.

Decimal

Description

This property returns or sets the decimal value of the expression.

This property is only valid with numeric fields.

This property is read-write.

ExpNum

Description

This property returns or sets the unique expression number, as a numeric value.

This property is read-write.

IsXlatExpression

Description

This property indicates whether the expression is for a translate field. This property takes a Boolean value: True, the expression is based on a translated field.

This property is read-only.

Length

Description

This property returns or sets the length of the expression, as a number.

This property is read-write.

Name

Description

This property returns the name of the expression, as a string.

This property is read-only.

OutputField

Description

This property returns the instance of the displayed expression field, as a QueryField. This property returns a NULL when the expression isn't used in a displayed (output) field.

This property is read-write.

RightExprFlag

Description

This property specifies whether an expression is used in the right-hand side of a criteria (that is, is it an Expr2 expression), as a Boolean value.

This property is typically used while reading a query to determine whether a Query Expression is used in the right-hand side of criteria. It's not necessary to set this value while saving a query to the database.

Values are: