Related Join Query Example

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>