Subquery Example

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>