Saving a Query
QAS provides a service operation to save the query in the PeopleSoft application database. The save query request must contain a name and at least one select, one record, and one field. This section describes the following service operations that are available to save a query:
QAS_SAVE_QUERY_OPER
QAS_SAVE_QUERY_REST_POST
This service operation validates and verifies XML-format query and saves the query in the PeopleSoft database.
Request Message: QAS_QUERY_SAVE_REQ_MSG
This message has several groupings, and depending on the specific query, some groupings may appear multiple times, while other groupings do not apply. The elements are listed here in groups.
<Records> Every request must contain at least one record. For each record, indicate the elements.
Records Elements All element tags are required if, unless indicated otherwise |
Description All element values are required except where noted |
---|---|
RCDNUM |
Record number. |
RCDNAME |
Record name. |
CORRNAME |
Alias name such as A,B,C, and so on. |
JOINTYPE |
Type of join. Valid values are:
Value is required only when the corresponding record is part of a JOIN in the query |
JOINRCDALIAS |
Record with which join is done. Value is required only when the corresponding record is part of a JOIN in the query. |
JOINFIELD |
Field with which join is done. Value is required only when the corresponding record is part of a JOIN in the query. |
RCDSELECTNUM |
Select number. |
<Fields> The request must contain at least one field. For each field, indicate the elements:
Fields Elements All element tags are required if, unless indicated otherwise |
Description All element values are required except where noted |
---|---|
FIELDNUM |
Field number. |
FIELDNAME |
Field name. When an expression is used as a field, the expression name is used for the field name and the value entered for this element is ignored. |
FIELDRCDNUM |
Record number. This element is ignored when an expression is used as a field. |
DESCR Not Required. |
Description. Value is not required. |
AGGREGRATE_TYPE |
Aggregation type. Valid values are:
|
HEADING_TYPE |
Heading type. Valid values are:
Note: If an expression is used as a field, the only valid value is Heading. |
HEADING |
If the HEADING_TYPE is Text, this element is used to enter the heading text. Note: Value is required when the HEADING_TYPE value is Text. The default heading for an expression used as a field is the expression text. |
COLUMNNUM |
Column number. |
ORDERBYNUM |
Order by number. |
ORDERBYDIR |
Direction of field ordering. Valid values are:
Note: Value is not required, the default is Ascending. |
FIELDSELECTNUM |
Identifier of SELECT in which this field is included. |
EXPRESSION_AS_FIELD |
Specify a valid expression number in order to use an expression as a field. For regular fields, leave this element blank. |
<Criteria> If the query contains criteria, all criteria elements are required. For each criteria, indicate the elements.
Criteria Elements All element tags are required to define criteria |
Description All element values are required except where noted |
---|---|
CRTNUM |
Criterion number. |
CRTNAME |
Criterion name. |
CRTHAVINGFLAG |
True if specifying a HAVING clause. Valid values are:
Note: Value is not required; the default is False. |
CRTSELECTNUM |
Identifier for the SELECT for this criterion. |
CRTNEGATION |
Negation in criterion. Valid values are:
Note: This is the equivalent of NOT in a SQL statement. |
CONDITION_TYPE |
Condition type in criterion. Valid values are:
|
LEFT_PARENTHESIS_LEVEL |
Left parenthesis level specified from 0 onwards. Used for GROUP BY. |
RIGHT_PARENTHESIS_LEVEL |
Right parenthesis level specified from 0 onwards. Used for GROUP BY. |
CRTEXP1TYPE |
Expression 1 type in criterion. Valid values are:
|
CRTEXP1TEXT |
Expression text for criterion 1. |
CRTEXP1NUM |
Expression number for the criteria. |
CRTEXP1RCDALIAS |
Alias for record used in expression 1 criterion. Used when CRTEXP1TYPE is Field. Alias name, such asA,B,C, and so on. |
CRTEXP1FIELD |
Field used in expression 1 criterion. Used when CRTEXP1TYPE is Field. |
CRTEXP2RCDALIAS |
Alias for record used in expression 2 criterion. Used when CRTEXP2TYPE is Field. |
CRTEXP2FIELD |
Field used in expression 2. |
CRTEXP2TYPE |
Expression 2 type in criterion. Valid values are:
|
CRTEXP2TEXT |
Expression 2 text is used to specify lists in A,B,C,D format, trees in string format, and prompt numbers. |
CRTLOGICALOPER |
Logical operator that links the criteria. Valid values are:
|
CRT_BELONGSTO |
If the criteria is for an outer join, you must indicate the correlation name (alias) that this criteria belongs to. Note: This element is used for a query that contains an Outer Join. For others, value for this element is null. |
<Expressions> If a query contains expressions, all expression elements are required. For each expression, indicate the elements.
Expressions Elements All element tags are required to define expression |
Description All element values are required except where noted |
---|---|
EXPNUM |
Expression number. |
EXPSELECTNUM |
Identifier of SELECT number for this expression. |
EXPNAME |
Expression name. |
EXPTYPE |
Expression type. Valid values are:
|
EXPLENGTH |
Expression length. |
EXPDECIMALPOS |
Number of decimal places in the expression. |
EXPTEXT |
Expression text. |
EXPAGGREGATE |
Specifies whether the expression is an aggregate function. |
<Prompts> If the query contains prompts, all prompt elements are required.
Prompts Elements All element tags are required to define prompt |
Description All element values are required except where noted |
---|---|
PROMPT_NUM |
Prompt number. |
PROMPT_NAME |
Prompt name. |
PROMPT_UNIQUE_NAME |
Unique prompt name. Note: This value is not required; however, the client application is responsible for ensuring that all default prompt names are unique. |
PROMPT_FLDNAME |
Name of the field used as prompt. |
PROMPT_TABLE |
Name of the prompt table. |
PROMPT_EDITTYPE |
Edit type. Valid values are:
|
PROMPT_HEADING |
Heading used for the prompt. |
PROMPT_HEADINGTYPE |
Type of prompt heading. Valid values are:
|
PROMPT_FLDLENGTH |
Field length for prompt. |
PROMPT_FLDDECIMALPOS |
Number of decimal positions in prompt. |
<Select> The request must contain at least one select. For each select, indicate the elements.
Select Elements All elements are required. |
Description All element values are required except where noted |
---|---|
SELECTNUM |
SELECT number. |
PARENTSELECTNUM |
Number of parent SELECT. For main SELECT, this must be set to 0. |
SELECTTYPE |
Type of selection. Valid values are:
|
QRYDISTINCT |
Indicates whether this query is distinct. Valid values are:
Note: Value is not required; the default is False. |
<Properties> The request must contain the properties that define the query. Indicate the property elements for the query.
Properties Elements All element tags are required |
Description All element values are required except where noted |
---|---|
QUERY_NAME |
Query name. |
DESCRIPTION |
Short description. Optional. |
DESCRLONG |
Long description. Optional. |
QUERY_OWNER |
Indicate the query owner. Valid values are:
Note: If the query owner is not specified, the save will fail. |
Depending on the complexity of the query, many different variations are available. Refer to the following sample queries.
Response Message: QAS_QUERY_SAVE_RESP_MSG
Element Name |
Description |
---|---|
STATUS |
Status of the save operation, either Success orFailure. |
INFO_MESSAGE |
Returns information about the save operation. |
Example Response:
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soapenv:Body>
<qcs:QAS_QUERY_SAVE_RESP_MSG xmlns:qcs="http://xmlns.oracle.com/
Enterprise/Tools/schemas/QAS_QUERY_SAVE_RESP_MSG.VERSION_1">
<INFO_MESSAGE>Query TEST1 saved successfully.</INFO_MESSAGE>
</qcs:QAS_QUERY_SAVE_RESP_MSG>
</soapenv:Body>
</soapenv:Envelope>
This is an example of a simple query TEST1 that has two columns. In this example, no criteria, expressions, or prompts exist. The SQL for this query is:
SELECT A.MESSAGE_SET_NBR, A.DESCR
FROM PSMSGSETDEFN A
SOAP document:
soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_QUERY_SAVE_REQ_MSG.VERSION_1">
<soapenv:Header/>
<soapenv:Body>
<qas:QAS_QUERY_SAVE_REQ_MSG>
<!--1 or more repetitions:-->
<RECORD>
<RCDNUM>1</RCDNUM>
<RCDNAME>PSMSGSETDEFN</RCDNAME>
<CORRNAME>A</CORRNAME>
<JOINTYPE></JOINTYPE>
<JOINRCDALIAS></JOINRCDALIAS>
<JOINFIELD></JOINFIELD>
<RCDSELECTNUM>1</RCDSELECTNUM>
</RECORD>
<!--1 or more repetitions:-->
<FIELD>
<FIELDNUM>1</FIELDNUM>
<FIELDNAME>MESSAGE_set_nbr</FIELDNAME>
<FIELDRCDNUM>1</FIELDRCDNUM>
<DESCR></DESCR>
<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
<HEADING_TYPE>RFT Short</HEADING_TYPE>
<HEADING></HEADING>
<COLUMNNUM>1</COLUMNNUM>
<ORDERBYNUM>1</ORDERBYNUM>
<ORDERBYDIR></ORDERBYDIR>
<FIELDSELECTNUM>1</FIELDSELECTNUM>
<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
</FIELD>
<FIELD>
<FIELDNUM>2</FIELDNUM>
<FIELDNAME>descr</FIELDNAME>
<FIELDRCDNUM>1</FIELDRCDNUM>
<DESCR></DESCR>
<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
<HEADING_TYPE>RFT Short</HEADING_TYPE>
<HEADING></HEADING>
<COLUMNNUM>2</COLUMNNUM>
<ORDERBYNUM>0</ORDERBYNUM>
<ORDERBYDIR></ORDERBYDIR>
<FIELDSELECTNUM>1</FIELDSELECTNUM>
<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
</FIELD>
<SELECT>
<SELECTNUM>1</SELECTNUM>
<PARENTSELECTNUM>0</PARENTSELECTNUM>
<SELECTTYPE>Main</SELECTTYPE>
<QRYDISTINCT></QRYDISTINCT>
</SELECT>
<QUERY_NAME>SimpleQuery</QUERY_NAME>
<DESCRIPTION>test a simple query</DESCRIPTION>
<DESCRLONG>Simple Query with one table and 2 fields</DESCRLONG>
<QUERY_OWNER>Public</QUERY_OWNER>
</qas:QAS_QUERY_SAVE_REQ_MSG>
</soapenv:Body>
</soapenv:Envelope>
This is an example of a query using two prompts, NODE_TYPE uses the translate table and PORTAL_NAME uses a prompt table. The SQL for this query is:
SELECT A.MSGNODENAME, A.VERSION, A.NODE_TYPE, A.PORTAL_NAME
FROM PSMSGNODEDEFN A
WHERE A.NODE_TYPE = :1
AND A.PORTAL_NAME = :2
SOAP document:
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_QUERY_SAVE_REQ_MSG.VERSION_1">
<soapenv:Header/>
<soapenv:Body>
<qas:QAS_QUERY_SAVE_REQ_MSG>
<!--1 or more repetitions:-->
<RECORD>
<RCDNUM>1</RCDNUM>
<RCDNAME>PSMSGNODEDEFN</RCDNAME>
<CORRNAME>A</CORRNAME>
<JOINTYPE></JOINTYPE>
<JOINRCDALIAS></JOINRCDALIAS>
<JOINFIELD></JOINFIELD>
<RCDSELECTNUM>1</RCDSELECTNUM>
</RECORD>
<!--1 or more repetitions:-->
<FIELD>
<FIELDNUM>1</FIELDNUM>
<FIELDNAME>MSGNODENAME</FIELDNAME>
<FIELDRCDNUM>1</FIELDRCDNUM>
<DESCR>Message Node Name</DESCR>
<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
<HEADING_TYPE>RFT Long</HEADING_TYPE>
<HEADING></HEADING>
<COLUMNNUM>1</COLUMNNUM>
<ORDERBYNUM>0</ORDERBYNUM>
<ORDERBYDIR></ORDERBYDIR>
<FIELDSELECTNUM>1</FIELDSELECTNUM>
<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
</FIELD>
<FIELD>
<FIELDNUM>2</FIELDNUM>
<FIELDNAME>VERSION</FIELDNAME>
<FIELDRCDNUM>1</FIELDRCDNUM>
<DESCR>Version</DESCR>
<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
<HEADING_TYPE>RFT Long</HEADING_TYPE>
<HEADING></HEADING>
<COLUMNNUM>2</COLUMNNUM>
<ORDERBYNUM>0</ORDERBYNUM>
<ORDERBYDIR></ORDERBYDIR>
<FIELDSELECTNUM>1</FIELDSELECTNUM>
<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
</FIELD>
<FIELD>
<FIELDNUM>3</FIELDNUM>
<FIELDNAME>node_type</FIELDNAME>
<FIELDRCDNUM>1</FIELDRCDNUM>
<DESCR></DESCR>
<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
<HEADING_TYPE>RFT Long</HEADING_TYPE>
<HEADING></HEADING>
<COLUMNNUM>3</COLUMNNUM>
<ORDERBYNUM>0</ORDERBYNUM>
<ORDERBYDIR></ORDERBYDIR>
<FIELDSELECTNUM>1</FIELDSELECTNUM>
<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
</FIELD>
<FIELD>
<FIELDNUM>4</FIELDNUM>
<FIELDNAME>PORTAL_NAME</FIELDNAME>
<FIELDRCDNUM>1</FIELDRCDNUM>
<DESCR>Portal</DESCR>
<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
<HEADING_TYPE>RFT Long</HEADING_TYPE>
<HEADING></HEADING>
<COLUMNNUM>4</COLUMNNUM>
<ORDERBYNUM>0</ORDERBYNUM>
<ORDERBYDIR></ORDERBYDIR>
<FIELDSELECTNUM>1</FIELDSELECTNUM>
<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
</FIELD>
<!--Zero or more repetitions:-->
<CRITERION>
<CRTNUM>1</CRTNUM>
<CRTNAME>1</CRTNAME>
<CRTHAVINGFLAG>False</CRTHAVINGFLAG>
<CRTSELECTNUM>1</CRTSELECTNUM>
<CRTNEGATION>False</CRTNEGATION>
<CONDITION_TYPE>equal to</CONDITION_TYPE>
<LEFT_PARENTHESIS_LEVEL>0</LEFT_PARENTHESIS_LEVEL>
<RIGHT_PARENTHESIS_LEVEL>0</RIGHT_PARENTHESIS_LEVEL>
<CRTEXP1TYPE>Field</CRTEXP1TYPE>
<CRTEXP1TEXT></CRTEXP1TEXT>
<CRTEXP1NUM></CRTEXP1NUM>
<CRTEXP1RCDALIAS>A</CRTEXP1RCDALIAS>
<CRTEXP1FIELD>NODE_TYPE</CRTEXP1FIELD>
<CRTEXP2RCDALIAS></CRTEXP2RCDALIAS>
<CRTEXP2FIELD></CRTEXP2FIELD>
<CRTEXP2TYPE>prompt</CRTEXP2TYPE>
<CRTEXP2TEXT>:1</CRTEXP2TEXT>
<CRTLOGICALOPER>not used</CRTLOGICALOPER>
</CRITERION>
<CRITERION>
<CRTNUM>2</CRTNUM>
<CRTNAME>2</CRTNAME>
<CRTHAVINGFLAG>False</CRTHAVINGFLAG>
<CRTSELECTNUM>1</CRTSELECTNUM>
<CRTNEGATION>False</CRTNEGATION>
<CONDITION_TYPE>equal to</CONDITION_TYPE>
<LEFT_PARENTHESIS_LEVEL>0</LEFT_PARENTHESIS_LEVEL>
<RIGHT_PARENTHESIS_LEVEL>0</RIGHT_PARENTHESIS_LEVEL>
<CRTEXP1TYPE>Field</CRTEXP1TYPE>
<CRTEXP1TEXT></CRTEXP1TEXT>
<CRTEXP1NUM></CRTEXP1NUM>
<CRTEXP1RCDALIAS>A</CRTEXP1RCDALIAS>
<CRTEXP1FIELD>portal_name</CRTEXP1FIELD>
<CRTEXP2RCDALIAS></CRTEXP2RCDALIAS>
<CRTEXP2FIELD></CRTEXP2FIELD>
<CRTEXP2TYPE>prompt</CRTEXP2TYPE>
<CRTEXP2TEXT>:2</CRTEXP2TEXT>
<CRTLOGICALOPER>not used</CRTLOGICALOPER>
</CRITERION>
<!--Zero or more repetitions:-->
<PROMPT>
<PROMPT_NUM>1</PROMPT_NUM>
<PROMPT_NAME>NodeType</PROMPT_NAME>
<PROMPT_UNIQUE_NAME>BIND1</PROMPT_UNIQUE_NAME>
<PROMPT_FLDNAME>NODE_TYPE</PROMPT_FLDNAME>
<PROMPT_TABLE></PROMPT_TABLE>
<PROMPT_EDITTYPE>Translate table</PROMPT_EDITTYPE>
<PROMPT_HEADING>Node Type</PROMPT_HEADING>
<PROMPT_HEADINGTYPE>Text</PROMPT_HEADINGTYPE>
<PROMPT_FLDLENGTH>30</PROMPT_FLDLENGTH>
<PROMPT_FLDDECIMALPOS>0</PROMPT_FLDDECIMALPOS>
</PROMPT>
<PROMPT>
<PROMPT_NUM>2</PROMPT_NUM>
<PROMPT_NAME>PortalName</PROMPT_NAME>
<PROMPT_UNIQUE_NAME>BIND2</PROMPT_UNIQUE_NAME>
<PROMPT_FLDNAME>PORTAL_TYPE</PROMPT_FLDNAME>
<PROMPT_TABLE>PSPRDMDEFN</PROMPT_TABLE>
<PROMPT_EDITTYPE>Prompt table</PROMPT_EDITTYPE>
<PROMPT_HEADING>Portal</PROMPT_HEADING>
<PROMPT_HEADINGTYPE>Text</PROMPT_HEADINGTYPE>
<PROMPT_FLDLENGTH>30</PROMPT_FLDLENGTH>
<PROMPT_FLDDECIMALPOS>0</PROMPT_FLDDECIMALPOS>
</PROMPT>
<!--1 or more repetitions:-->
<SELECT>
<SELECTNUM>1</SELECTNUM>
<PARENTSELECTNUM>0</PARENTSELECTNUM>
<SELECTTYPE>Main</SELECTTYPE>
<QRYDISTINCT>False</QRYDISTINCT>
</SELECT>
<QUERY_NAME>Query_with_prompt</QUERY_NAME>
<DESCRIPTION>Prompt example</DESCRIPTION>
<DESCRLONG>Query prompts for node type and portal name</DESCRLONG>
<QUERY_OWNER>public</QUERY_OWNER>
</qas:QAS_QUERY_SAVE_REQ_MSG>
</soapenv:Body>
</soapenv:Envelope>
In a related record join, you can automatically join two records based on a relationship that has been predefined in the record designer. For example, if a field has a prompt table defined for it, PeopleSoft Query displays a join link to the right of the shared field.
This SQL creates a query with a related join:
SELECT A.PRCSJOBNAME, A.PRCSTYPE, B.SERVERNAME
FROM PS_PRCSJOBDEFN A, PS_SERVERDEFN B
WHERE B.SERVERNAME = A.SERVERNAME
SOAP document:
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_QUERY_SAVE_REQ_MSG.VERSION_1">
<soapenv:Header/>
<soapenv:Body>
<qas:QAS_QUERY_SAVE_REQ_MSG>
<!--1 or more repetitions:-->
<RECORD>
<RCDNUM>1</RCDNUM>
<RCDNAME>PRCSJOBDEFN</RCDNAME>
<CORRNAME>A</CORRNAME>
<JOINTYPE></JOINTYPE>
<JOINRCDALIAS></JOINRCDALIAS>
<JOINFIELD></JOINFIELD>
<RCDSELECTNUM>1</RCDSELECTNUM>
</RECORD>
<RECORD>
<RCDNUM>2</RCDNUM>
<RCDNAME>SERVERDEFN</RCDNAME>
<CORRNAME>B</CORRNAME>
<JOINTYPE>related</JOINTYPE>
<JOINRCDALIAS>A</JOINRCDALIAS>
<JOINFIELD>SERVERNAME</JOINFIELD>
<RCDSELECTNUM>1</RCDSELECTNUM>
</RECORD>
<!--1 or more repetitions:-->
<FIELD>
<FIELDNUM>1</FIELDNUM>
<FIELDNAME>PRCSJOBNAME</FIELDNAME>
<FIELDRCDNUM>1</FIELDRCDNUM>
<DESCR>Process Job Name</DESCR>
<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
<HEADING_TYPE>RFT Long</HEADING_TYPE>
<HEADING></HEADING>
<COLUMNNUM>1</COLUMNNUM>
<ORDERBYNUM>0</ORDERBYNUM>
<ORDERBYDIR>Ascending</ORDERBYDIR>
<FIELDSELECTNUM>1</FIELDSELECTNUM>
<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
</FIELD>
<FIELD>
<FIELDNUM>2</FIELDNUM>
<FIELDNAME>PRCSTYPE</FIELDNAME>
<FIELDRCDNUM>1</FIELDRCDNUM>
<DESCR>Process Type</DESCR>
<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
<HEADING_TYPE>RFT Long</HEADING_TYPE>
<HEADING></HEADING>
<COLUMNNUM>2</COLUMNNUM>
<ORDERBYNUM>0</ORDERBYNUM>
<ORDERBYDIR>Ascending</ORDERBYDIR>
<FIELDSELECTNUM>1</FIELDSELECTNUM>
<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
</FIELD>
<FIELD>
<FIELDNUM>3</FIELDNUM>
<FIELDNAME>SERVERNAME</FIELDNAME>
<FIELDRCDNUM>2</FIELDRCDNUM>
<DESCR>Server Name</DESCR>
<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
<HEADING_TYPE>RFT Long</HEADING_TYPE>
<HEADING></HEADING>
<COLUMNNUM>3</COLUMNNUM>
<ORDERBYNUM>0</ORDERBYNUM>
<ORDERBYDIR>Ascending</ORDERBYDIR>
<FIELDSELECTNUM>1</FIELDSELECTNUM>
<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
</FIELD>
<!--1 or more repetitions:-->
<SELECT>
<SELECTNUM>1</SELECTNUM>
<PARENTSELECTNUM>0</PARENTSELECTNUM>
<SELECTTYPE>Main</SELECTTYPE>
<QRYDISTINCT>False</QRYDISTINCT>
</SELECT>
<QUERY_NAME>RelatedJoin</QUERY_NAME>
<DESCRIPTION>Related Join example</DESCRIPTION>
<DESCRLONG>Query with related join</DESCRLONG>
<QUERY_OWNER>Public</QUERY_OWNER>
</qas:QAS_QUERY_SAVE_REQ_MSG>
</soapenv:Body>
</soapenv:Envelope>
This SQL creates a query with a related left outer join:
SELECT A.PORTAL_NAME
FROM (PSMSGNODEDEFN A LEFT OUTER JOIN PSPRDMDEFN B ON B.PORTAL_NAME = A.PORTAL_NAME )
SOAP document:
<QAS_QUERY_SAVE_REQ_MSG>
<RECORD>
<RCDNUM>1</RCDNUM>
<RCDNAME>PSMSGNODEDEFN</RCDNAME>
<CORRNAME>A</CORRNAME>
<JOINTYPE/>
<JOINRCDALIAS/>
<JOINFIELD/>
<RCDSELECTNUM>1</RCDSELECTNUM>
</RECORD>
<RECORD>
<RCDNUM>2</RCDNUM>
<RCDNAME>PSPRDMDEFN</RCDNAME>
<CORRNAME>B</CORRNAME>
<JOINRCDALIAS>A</JOINRCDALIAS>
<JOINFIELD>PORTAL_NAME</JOINFIELD>
<JOINTYPE>RelatedLeftOuter</JOINTYPE>
<RCDSELECTNUM>1</RCDSELECTNUM>
</RECORD>
<FIELD>
<FIELDNUM>1</FIELDNUM>
<FIELDNAME>PORTAL_NAME</FIELDNAME>
<FIELDRCDNUM>1</FIELDRCDNUM>
<DESCR>Portal Name</DESCR>
<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
<HEADING_TYPE>RFT Short</HEADING_TYPE>
<HEADING>Portal Name</HEADING>
<COLUMNNUM>1</COLUMNNUM>
<ORDERBYNUM>0</ORDERBYNUM>
<ORDERBYDIR>Ascending</ORDERBYDIR>
<FIELDSELECTNUM>1</FIELDSELECTNUM>
<EXPRESSION_AS_FIELD>0</EXPRESSION_AS_FIELD>
</FIELD>
<CRITERION>
<CRTNUM>1</CRTNUM>
<CRTNAME>1</CRTNAME>
<CRTHAVINGFLAG>False</CRTHAVINGFLAG>
<CRTSELECTNUM>1</CRTSELECTNUM>
<CRTNEGATION>False</CRTNEGATION>
<CONDITION_TYPE>equal to</CONDITION_TYPE>
<LEFT_PARENTHESIS_LEVEL>0</LEFT_PARENTHESIS_LEVEL>
<RIGHT_PARENTHESIS_LEVEL>0</RIGHT_PARENTHESIS_LEVEL>
<CRTEXP1TYPE>Field</CRTEXP1TYPE>
<CRTEXP1TEXT>B.PORTAL_NAME</CRTEXP1TEXT>
<CRTEXP1NUM>0</CRTEXP1NUM>
<CRTEXP1RCDALIAS>B</CRTEXP1RCDALIAS>
<CRTEXP1FIELD>PORTAL_NAME</CRTEXP1FIELD>
<CRTEXP2RCDALIAS>A</CRTEXP2RCDALIAS>
<CRTEXP2FIELD>PORTAL_NAME</CRTEXP2FIELD>
<CRTEXP2TYPE>Field</CRTEXP2TYPE>
<CRTEXP2TEXT>A.PORTAL_NAME</CRTEXP2TEXT>
<CRTLOGICALOPER>not used</CRTLOGICALOPER>
<CRT_BELONGSTO>B</CRT_BELONGSTO>
</CRITERION>
<SELECT>
<SELECTNUM>1</SELECTNUM>
<PARENTSELECTNUM>0</PARENTSELECTNUM>
<SELECTTYPE>Main</SELECTTYPE>
<QRYDISTINCT>False</QRYDISTINCT>
</SELECT>
<QUERY_NAME>OJ_TRIAL</QUERY_NAME>
<DESCRIPTION>This is a query with a left outer join</DESCRIPTION>
<DESCRLONG>outer join example used for testing with QAS</DESCRLONG>
<QUERY_OWNER>Public</QUERY_OWNER>
</QAS_QUERY_SAVE_REQ_MSG>
A record hierarchy join joins a parent table to a child table. A child table is a table that uses all the same key fields as its parent, plus one or more additional keys. The SQL for this query is:
SELECT A.PRCSNAME, A.PRCSTYPE, B.PRCSGRP
FROM PS_PRCSDEFN A, PS_PRCSDEFNGRP B
WHERE B.PRCSTYPE = A.PRCSTYPE AND B.PRCSNAME = A.PRCSNAME
Soap document:
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_QUERY_SAVE_REQ_MSG.VERSION_1">
<soapenv:Header/>
<soapenv:Body>
<qas:QAS_QUERY_SAVE_REQ_MSG>
<!--1 or more repetitions:-->
<RECORD>
<RCDNUM>1</RCDNUM>
<RCDNAME>PRCSDEFN</RCDNAME>
<CORRNAME>A</CORRNAME>
<JOINTYPE></JOINTYPE>
<JOINRCDALIAS></JOINRCDALIAS>
<JOINFIELD></JOINFIELD>
<RCDSELECTNUM>1</RCDSELECTNUM>
</RECORD>
<RECORD>
<RCDNUM>2</RCDNUM>
<RCDNAME>PRCSDEFNGRP</RCDNAME>
<CORRNAME>B</CORRNAME>
<JOINTYPE>hierarchy</JOINTYPE>
<JOINRCDALIAS>A</JOINRCDALIAS>
<JOINFIELD></JOINFIELD>
<RCDSELECTNUM>1</RCDSELECTNUM>
</RECORD>
<!--1 or more repetitions:-->
<FIELD>
<FIELDNUM>1</FIELDNUM>
<FIELDNAME>PRCSNAME</FIELDNAME>
<FIELDRCDNUM>1</FIELDRCDNUM>
<DESCR></DESCR>
<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
<HEADING_TYPE>RFT Long</HEADING_TYPE>
<HEADING></HEADING>
<COLUMNNUM>1</COLUMNNUM>
<ORDERBYNUM>0</ORDERBYNUM>
<ORDERBYDIR></ORDERBYDIR>
<FIELDSELECTNUM>1</FIELDSELECTNUM>
<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
</FIELD>
<FIELD>
<FIELDNUM>2</FIELDNUM>
<FIELDNAME>PRCSTYPE</FIELDNAME>
<FIELDRCDNUM>1</FIELDRCDNUM>
<DESCR></DESCR>
<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
<HEADING_TYPE>RFT Long</HEADING_TYPE>
<HEADING></HEADING>
<COLUMNNUM>2</COLUMNNUM>
<ORDERBYNUM>0</ORDERBYNUM>
<ORDERBYDIR></ORDERBYDIR>
<FIELDSELECTNUM>1</FIELDSELECTNUM>
<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
</FIELD>
<FIELD>
<FIELDNUM>3</FIELDNUM>
<FIELDNAME>PRCSGRP</FIELDNAME>
<FIELDRCDNUM>2</FIELDRCDNUM>
<DESCR></DESCR>
<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
<HEADING_TYPE>RFT Short</HEADING_TYPE>
<HEADING></HEADING>
<COLUMNNUM>3</COLUMNNUM>
<ORDERBYNUM>0</ORDERBYNUM>
<ORDERBYDIR></ORDERBYDIR>
<FIELDSELECTNUM>1</FIELDSELECTNUM>
<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
</FIELD>
<!--1 or more repetitions:-->
<SELECT>
<SELECTNUM>1</SELECTNUM>
<PARENTSELECTNUM>0</PARENTSELECTNUM>
<SELECTTYPE>Main</SELECTTYPE>
<QRYDISTINCT>False</QRYDISTINCT>
</SELECT>
<QUERY_NAME>HierarchyJoin</QUERY_NAME>
<DESCRIPTION>Hierarchy Join example</DESCRIPTION>
<DESCRLONG>Thios is a query with a hierarchy join</DESCRLONG>
<QUERY_OWNER>public</QUERY_OWNER>
</qas:QAS_QUERY_SAVE_REQ_MSG>
</soapenv:Body>
</soapenv:Envelope>
This is an example of a query that contains the aggregate count for EMPLID. The SQL for this query is:
SELECT A.MESSAGE_SET_NBR, COUNT(*)
FROM PSMSGCATDEFN A
GROUP BY A.MESSAGE_SET_NBR
ORDER BY 1
SOAP document:
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_QUERY_SAVE_REQ_MSG.VERSION_1">
<soapenv:Header/>
<soapenv:Body>
<qas:QAS_QUERY_SAVE_REQ_MSG>
<!--1 or more repetitions:-->
<RECORD>
<RCDNUM>1</RCDNUM>
<RCDNAME>PSMSGCATDEFN</RCDNAME>
<CORRNAME>A</CORRNAME>
<JOINTYPE></JOINTYPE>
<JOINRCDALIAS></JOINRCDALIAS>
<JOINFIELD></JOINFIELD>
<RCDSELECTNUM>1</RCDSELECTNUM>
</RECORD>
<!--1 or more repetitions:-->
<FIELD>
<FIELDNUM>1</FIELDNUM>
<FIELDNAME>MESSAGE_SET_NBR</FIELDNAME>
<FIELDRCDNUM>1</FIELDRCDNUM>
<DESCR></DESCR>
<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
<HEADING_TYPE>RFT Long</HEADING_TYPE>
<HEADING></HEADING>
<COLUMNNUM>1</COLUMNNUM>
<ORDERBYNUM>1</ORDERBYNUM>
<ORDERBYDIR></ORDERBYDIR>
<FIELDSELECTNUM>1</FIELDSELECTNUM>
<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
</FIELD>
<FIELD>
<FIELDNUM>2</FIELDNUM>
<FIELDNAME>MESSAGE_NBR</FIELDNAME>
<FIELDRCDNUM>1</FIELDRCDNUM>
<DESCR></DESCR>
<AGGREGATE_TYPE>Count</AGGREGATE_TYPE>
<HEADING_TYPE>Text</HEADING_TYPE>
<HEADING>Count</HEADING>
<COLUMNNUM>2</COLUMNNUM>
<ORDERBYNUM>0</ORDERBYNUM>
<ORDERBYDIR></ORDERBYDIR>
<FIELDSELECTNUM>1</FIELDSELECTNUM>
<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
</FIELD>
<!--1 or more repetitions:-->
<SELECT>
<SELECTNUM>1</SELECTNUM>
<PARENTSELECTNUM>0</PARENTSELECTNUM>
<SELECTTYPE>Main</SELECTTYPE>
<QRYDISTINCT></QRYDISTINCT>
</SELECT>
<QUERY_NAME>CountExample</QUERY_NAME>
<DESCRIPTION>Example using count</DESCRIPTION>
<DESCRLONG>
This query counts number of messages in
each message set
</DESCRLONG>
<QUERY_OWNER>public</QUERY_OWNER>
</qas:QAS_QUERY_SAVE_REQ_MSG>
</soapenv:Body>
</soapenv:Envelope>
This is an example of a query that contains an expression for total price. The SQL for this query is:
SELECT A.QE_ORDER_NBR, A.QE_ORDER_LINE_NBR, A.QE_QTY * A.QE_PRICE
FROM PS_QEORDER_DTL A
ORDER BY 1
SOAP document:
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_QUERY_SAVE_REQ_MSG.VERSION_1">
<soapenv:Header/>
<soapenv:Body>
<qas:QAS_QUERY_SAVE_REQ_MSG>
<!--1 or more repetitions:-->
<RECORD>
<RCDNUM>1</RCDNUM>
<RCDNAME>qeorder_dtl</RCDNAME>
<CORRNAME>A</CORRNAME>
<JOINTYPE></JOINTYPE>
<JOINRCDALIAS></JOINRCDALIAS>
<JOINFIELD></JOINFIELD>
<RCDSELECTNUM>1</RCDSELECTNUM>
</RECORD>
<!--1 or more repetitions:-->
<FIELD>
<FIELDNUM>1</FIELDNUM>
<FIELDNAME>QE_ORDER_NBR</FIELDNAME>
<FIELDRCDNUM>1</FIELDRCDNUM>
<DESCR></DESCR>
<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
<HEADING_TYPE>RFT Short</HEADING_TYPE>
<HEADING></HEADING>
<COLUMNNUM>1</COLUMNNUM>
<ORDERBYNUM>1</ORDERBYNUM>
<ORDERBYDIR></ORDERBYDIR>
<FIELDSELECTNUM>1</FIELDSELECTNUM>
<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
</FIELD>
<FIELD>
<FIELDNUM>2</FIELDNUM>
<FIELDNAME>QE_ORDER_LINE_NBR</FIELDNAME>
<FIELDRCDNUM>1</FIELDRCDNUM>
<DESCR></DESCR>
<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
<HEADING_TYPE>RFT Short</HEADING_TYPE>
<HEADING></HEADING>
<COLUMNNUM>2</COLUMNNUM>
<ORDERBYNUM></ORDERBYNUM>
<ORDERBYDIR></ORDERBYDIR>
<FIELDSELECTNUM>1</FIELDSELECTNUM>
<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
</FIELD>
<FIELD>
<FIELDNUM>3</FIELDNUM>
<FIELDNAME></FIELDNAME>
<FIELDRCDNUM>1</FIELDRCDNUM>
<DESCR></DESCR>
<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
<HEADING_TYPE>Text</HEADING_TYPE>
<HEADING>Total Line Amount</HEADING>
<COLUMNNUM>3</COLUMNNUM>
<ORDERBYNUM>1</ORDERBYNUM>
<ORDERBYDIR></ORDERBYDIR>
<FIELDSELECTNUM>1</FIELDSELECTNUM>
<EXPRESSION_AS_FIELD>1</EXPRESSION_AS_FIELD>
</FIELD>
<!--Zero or more repetitions:-->
<EXPRESSION>
<EXPNUM>1</EXPNUM>
<EXPSELECTNUM>1</EXPSELECTNUM>
<EXPNAME>1</EXPNAME>
<EXPTYPE>number</EXPTYPE>
<EXPLENGTH>11</EXPLENGTH>
<EXPDECIMALPOS>2</EXPDECIMALPOS>
<EXPTEXT>A.QE_QTY * A.QE_PRICE</EXPTEXT>
<EXPAGGREGATE>False</EXPAGGREGATE>
</EXPRESSION>
<!--1 or more repetitions:-->
<SELECT>
<SELECTNUM>1</SELECTNUM>
<PARENTSELECTNUM>0</PARENTSELECTNUM>
<SELECTTYPE>Main</SELECTTYPE>
<QRYDISTINCT>False</QRYDISTINCT>
</SELECT>
<QUERY_NAME>test_expression</QUERY_NAME>
<DESCRIPTION>Test using expression as field</DESCRIPTION>
<DESCRLONG></DESCRLONG>
<QUERY_OWNER>Public</QUERY_OWNER>
</qas:QAS_QUERY_SAVE_REQ_MSG>
</soapenv:Body>
</soapenv:Envelope>
This is an example of a query that contains a subquery. The SQL for this query is:
SELECT DISTINCT A.EMPLID, A.QE_EMPLOYEE_NAME FROM PS_QE_EMPLOYEE A WHERE A.EMPLID IN (SELECT B.QE_EMPLID FROM PS_QE_PERS_DATA B WHERE ( B.QE_HIGHLY_COMP_EMP = 'E')) ORDER BY 2
SOAP document:
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_QUERY_SAVE_REQ_MSG.VERSION_1">
<soapenv:Header/>
<soapenv:Body>
<qas:QAS_QUERY_SAVE_REQ_MSG>
<!--1 or more repetitions:-->
<RECORD>
<RCDNUM>1</RCDNUM>
<RCDNAME>QE_EMPLOYEE</RCDNAME>
<CORRNAME>A</CORRNAME>
<JOINTYPE/>
<JOINRCDALIAS/>
<JOINFIELD/>
<RCDSELECTNUM>1</RCDSELECTNUM>
</RECORD>
<RECORD>
<RCDNUM>2</RCDNUM>
<RCDNAME>QE_PERS_DATA</RCDNAME>
<CORRNAME>B</CORRNAME>
<JOINTYPE/>
<JOINRCDALIAS/>
<JOINFIELD/>
<RCDSELECTNUM>2</RCDSELECTNUM>
</RECORD>
<!--1 or more repetitions:-->
<FIELD>
<FIELDNUM>1</FIELDNUM>
<FIELDNAME>EMPLID</FIELDNAME>
<FIELDRCDNUM>1</FIELDRCDNUM>
<DESCR>Emplid</DESCR>
<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
<HEADING_TYPE>text</HEADING_TYPE>
<HEADING>Employee ID</HEADING>
<COLUMNNUM>1</COLUMNNUM>
<ORDERBYNUM>0</ORDERBYNUM>
<ORDERBYDIR>Ascending</ORDERBYDIR>
<FIELDSELECTNUM>1</FIELDSELECTNUM>
<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
</FIELD>
<FIELD>
<FIELDNUM>2</FIELDNUM>
<FIELDNAME>QE_EMPLOYEE_NAME</FIELDNAME>
<FIELDRCDNUM>1</FIELDRCDNUM>
<DESCR>Name</DESCR>
<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
<HEADING_TYPE>text</HEADING_TYPE>
<HEADING>Name</HEADING>
<COLUMNNUM>2</COLUMNNUM>
<ORDERBYNUM>1</ORDERBYNUM>
<ORDERBYDIR>Ascending</ORDERBYDIR>
<FIELDSELECTNUM>1</FIELDSELECTNUM>
<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
</FIELD>
<FIELD>
<FIELDNUM>3</FIELDNUM>
<FIELDNAME>QE_EMPLID</FIELDNAME>
<FIELDRCDNUM>2</FIELDRCDNUM>
<DESCR>qeEmplid</DESCR>
<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
<HEADING_TYPE>text</HEADING_TYPE>
<HEADING>Employee ID</HEADING>
<COLUMNNUM>1</COLUMNNUM>
<ORDERBYNUM>0</ORDERBYNUM>
<ORDERBYDIR>Ascending</ORDERBYDIR>
<FIELDSELECTNUM>2</FIELDSELECTNUM>
<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
</FIELD>
<!--Zero or more repetitions:-->
<CRITERION>
<CRTNUM>1</CRTNUM>
<CRTNAME>myCrit</CRTNAME>
<CRTHAVINGFLAG>False</CRTHAVINGFLAG>
<CRTSELECTNUM>1</CRTSELECTNUM>
<CRTNEGATION>False</CRTNEGATION>
<CONDITION_TYPE>in list</CONDITION_TYPE>
<LEFT_PARENTHESIS_LEVEL>0</LEFT_PARENTHESIS_LEVEL>
<RIGHT_PARENTHESIS_LEVEL>0</RIGHT_PARENTHESIS_LEVEL>
<CRTEXP1TYPE>Field</CRTEXP1TYPE>
<CRTEXP1TEXT>1</CRTEXP1TEXT>
<CRTEXP1NUM>1</CRTEXP1NUM>
<CRTEXP1RCDALIAS>A</CRTEXP1RCDALIAS>
<CRTEXP1FIELD>EMPLID</CRTEXP1FIELD>
<CRTEXP2RCDALIAS/>
<CRTEXP2FIELD/>
<CRTEXP2TYPE>subquery</CRTEXP2TYPE>
<CRTEXP2TEXT>2</CRTEXP2TEXT>
<CRTLOGICALOPER>not used</CRTLOGICALOPER>
</CRITERION>
<CRITERION>
<CRTNUM>2</CRTNUM>
<CRTNAME>subCrit</CRTNAME>
<CRTHAVINGFLAG>False</CRTHAVINGFLAG>
<CRTSELECTNUM>2</CRTSELECTNUM>
<CRTNEGATION>False</CRTNEGATION>
<CONDITION_TYPE>equal to</CONDITION_TYPE>
<LEFT_PARENTHESIS_LEVEL>1</LEFT_PARENTHESIS_LEVEL>
<RIGHT_PARENTHESIS_LEVEL>1</RIGHT_PARENTHESIS_LEVEL>
<CRTEXP1TYPE>Field</CRTEXP1TYPE>
<CRTEXP1TEXT>1</CRTEXP1TEXT>
<CRTEXP1NUM>1</CRTEXP1NUM>
<CRTEXP1RCDALIAS>B</CRTEXP1RCDALIAS>
<CRTEXP1FIELD>QE_HIGHLY_COMP_EMP</CRTEXP1FIELD>
<CRTEXP2RCDALIAS/>
<CRTEXP2FIELD/>
<CRTEXP2TYPE>Constant</CRTEXP2TYPE>
<CRTEXP2TEXT>E</CRTEXP2TEXT>
<CRTLOGICALOPER>not used</CRTLOGICALOPER>
</CRITERION>
<!--1 or more repetitions:-->
<SELECT>
<SELECTNUM>1</SELECTNUM>
<PARENTSELECTNUM>0</PARENTSELECTNUM>
<SELECTTYPE>Main</SELECTTYPE>
<QRYDISTINCT>True</QRYDISTINCT>
</SELECT><SELECT>
<SELECTNUM>2</SELECTNUM>
<PARENTSELECTNUM>1</PARENTSELECTNUM>
<SELECTTYPE>Subquery</SELECTTYPE>
<QRYDISTINCT>False</QRYDISTINCT>
</SELECT>
<QUERY_NAME>QuerywithSubQuery</QUERY_NAME>
<DESCRIPTION>This is a query with a subquery</DESCRIPTION>
<DESCRLONG>Subquery example used for testing with QAS</DESCRLONG>
<QUERY_OWNER>Public</QUERY_OWNER>
</qas:QAS_QUERY_SAVE_REQ_MSG>
</soapenv:Body>
</soapenv:Envelope>
This service operation validates and verifies XML-format query and saves the query in the PeopleSoft database.
Request Message: QAS_QUERY_SAVE_TEMPL
Example Request:.
http://<servername>:<port>/PSIGW/RESTListeningConnector/<defaultlocal node>/QuerySave.v1/
Response Message: QAS_QUERY_SAVE_RES_MSG
Example Response:.
<?xml version="1.0"?>
<qcs:QAS_QUERY_SAVE_RESP_MSG xmlns:qcs="http://xmlns.oracle.com/Enterprise/
Tools/schemas/QAS_QUERY_SAVE_RESP_MSG.VERSION_1">
<INFO_MESSAGE>Query SimpleQuery saved successfully.</INFO_MESSAGE>
<STATUS>Success</STATUS>
</qcs:QAS_QUERY_SAVE_RESP_MSG>