Query Using Prompts Example

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>