161 lines
11 KiB
HTML
161 lines
11 KiB
HTML
<?xml version="1.0" encoding="UTF-8"?>
|
||
<!DOCTYPE html
|
||
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
||
<html lang="en-us" xml:lang="en-us">
|
||
<head>
|
||
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
||
<meta name="security" content="public" />
|
||
<meta name="Robots" content="index,follow" />
|
||
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
||
<meta name="DC.Type" content="reference" />
|
||
<meta name="DC.Title" content="Example 1: Select records using the Open Query File (OPNQRYF) command" />
|
||
<meta name="abstract" content="This example shows how to select records with a specific value using the Open Query File (OPNQRYF) command." />
|
||
<meta name="description" content="This example shows how to select records with a specific value using the Open Query File (OPNQRYF) command." />
|
||
<meta name="DC.Relation" scheme="URI" content="rbafodynsrsex.htm" />
|
||
<meta name="DC.Relation" scheme="URI" content="rbafoappdbcs.htm" />
|
||
<meta name="DC.Relation" scheme="URI" content="rbafoqryprf.htm" />
|
||
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
<meta name="DC.Format" content="XHTML" />
|
||
<meta name="DC.Identifier" content="rbafodynsrex1" />
|
||
<meta name="DC.Language" content="en-us" />
|
||
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
||
<!-- US Government Users Restricted Rights -->
|
||
<!-- Use, duplication or disclosure restricted by -->
|
||
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
||
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
||
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
||
<title>Example 1: Select records using the Open Query File (OPNQRYF) command</title>
|
||
</head>
|
||
<body id="rbafodynsrex1"><a name="rbafodynsrex1"><!-- --></a>
|
||
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
<h1 class="topictitle1">Example 1: Select records using the Open Query File (OPNQRYF) command</h1>
|
||
<div><p>This example shows how to select records with a specific value
|
||
using the Open Query File (OPNQRYF) command.</p>
|
||
<div class="section"><div class="p">Assume that you want to select all the records from
|
||
FILEA where the value of the <em>Code</em> field is D. Your processing program
|
||
is PGMB. PGMB only sees the records that meet the selection value (you do
|
||
not have to test in your program).<div class="note"><span class="notetitle">Note:</span> You can specify parameters easier
|
||
by using the prompt function for the OPNQRYF command. For example, you can
|
||
specify an expression for the QRYSLT parameter without the surrounding delimiters
|
||
because the system will add the single quotation marks.</div>
|
||
</div>
|
||
</div>
|
||
<div class="section"><div class="p">Specify the following parameters: <pre>OVRDBF FILE(FILEA) SHARE(*YES)
|
||
OPNQRYF FILE(FILEA) QRYSLT('CODE *EQ "D" ')
|
||
CALL PGM(PGMB)
|
||
CLOF OPNID(FILEA)
|
||
DLTOVR FILE(FILEA)</pre>
|
||
<div class="note"><span class="notetitle">Notes:</span> <ol><li>The entire expression in the QRYSLT parameter must be enclosed in single
|
||
quotation marks (' ').</li>
|
||
<li>When specifying field names in the OPNQRYF command, the names in the record
|
||
are not enclosed in quotation marks.</li>
|
||
<li>Character literals must be enclosed by quotation marks (" "). (The quotation
|
||
mark character is used in the examples.) It is important to place the character(s)
|
||
between the quotation marks in either uppercase or lowercase to match the
|
||
value you want to find in the database. (The examples are all shown in uppercase.)</li>
|
||
<li>To request a selection against a numeric constant, specify: <pre>OPNQRYF FILE(FILEA) QRYSLT('AMT *GT 1000.00')</pre>
|
||
<div class="note"><span class="notetitle">Note:</span> </div>
|
||
<p>Numeric constants are <em>not</em> enclosed by quotation marks.</p>
|
||
</li>
|
||
<li>When comparing a field value to a control language (CL) variable, use
|
||
apostrophes as follows (only character CL variables can be used): <ul><li>If doing selection against a character, date, time, or timestamp field,
|
||
specify: <pre>OPNQRYF FILE(FILEA) QRYSLT('"' *CAT &CHAR *CAT '" *EQ FIELDA')</pre>
|
||
or,
|
||
in reverse order: <pre>OPNQRYF FILE(FILEA) QRYSLT('FIELDA *EQ "' *CAT &CHAR *CAT '"')</pre>
|
||
<div class="note"><span class="notetitle">Note:</span> </div>
|
||
Single
|
||
quotation marks (' ') and quotation marks (" ") enclose the CL variables and
|
||
*CAT operators.</li>
|
||
<li>If doing selection against a numeric field, specify: <pre>OPNQRYF FILE(FILEA) QRYSLT(&CHARNUM *CAT ' *EQ NUM')</pre>
|
||
or,
|
||
in reverse order: <pre>OPNQRYF FILE(FILEA) QRYSLT('NUM *EQ ' *CAT &CHARNUM);</pre>
|
||
<div class="note"><span class="notetitle">Note:</span> </div>
|
||
Single
|
||
quotation marks enclose the field and operator only.</li>
|
||
</ul>
|
||
</li>
|
||
</ol>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
<div class="section"><p>When comparing two fields or constants, the data types must be
|
||
compatible. The following table describes the valid comparisons. </p>
|
||
</div>
|
||
|
||
<div class="tablenoborder"><a name="rbafodynsrex1__rbafovnc"><!-- --></a><table cellpadding="4" cellspacing="0" summary="" id="rbafodynsrex1__rbafovnc" width="100%" frame="hsides" border="0" rules="none"><caption>Table 1. Valid data type comparisons for the OPNQRYF command</caption><thead align="left"><tr><th align="left" valign="bottom" width="16.666666666666664%" class="firstcol" id="d0e83"> </th>
|
||
<th align="left" valign="bottom" width="16.666666666666664%" id="d0e85">Any numeric</th>
|
||
<th align="left" valign="bottom" width="16.666666666666664%" id="d0e87">Character</th>
|
||
<th align="left" valign="bottom" width="16.666666666666664%" id="d0e89">Date<sup>1</sup></th>
|
||
<th align="left" valign="bottom" width="16.666666666666664%" id="d0e93">Time<sup>1</sup></th>
|
||
<th align="left" valign="bottom" width="16.666666666666664%" id="d0e97">Timestamp<sup>1</sup></th>
|
||
</tr>
|
||
</thead>
|
||
<tbody><tr><th align="left" valign="top" width="16.666666666666664%" class="firstcol" id="d0e103" headers="d0e83 ">Any numeric</th>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e103 d0e85 ">Valid</td>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e103 d0e87 ">Not valid</td>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e103 d0e89 ">Not valid</td>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e103 d0e93 ">Not valid</td>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e103 d0e97 ">Not valid</td>
|
||
</tr>
|
||
<tr><th align="left" valign="top" width="16.666666666666664%" class="firstcol" id="d0e116" headers="d0e83 ">Character</th>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e116 d0e85 ">Not valid</td>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e116 d0e87 ">Valid</td>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e116 d0e89 ">Valid<sup>2</sup></td>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e116 d0e93 ">Valid<sup>2</sup></td>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e116 d0e97 ">Valid<sup>2</sup></td>
|
||
</tr>
|
||
<tr><th align="left" valign="top" width="16.666666666666664%" class="firstcol" id="d0e135" headers="d0e83 ">Date<sup>1</sup></th>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e135 d0e85 ">Not valid</td>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e135 d0e87 ">Valid<sup>2</sup></td>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e135 d0e89 ">Valid</td>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e135 d0e93 ">Not valid</td>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e135 d0e97 ">Not valid</td>
|
||
</tr>
|
||
<tr><th align="left" valign="top" width="16.666666666666664%" class="firstcol" id="d0e152" headers="d0e83 ">Time<sup>1</sup></th>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e152 d0e85 ">Not valid</td>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e152 d0e87 ">Valid<sup>2</sup></td>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e152 d0e89 ">Not valid</td>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e152 d0e93 ">Valid</td>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e152 d0e97 ">Not valid</td>
|
||
</tr>
|
||
<tr><th align="left" valign="top" width="16.666666666666664%" class="firstcol" id="d0e169" headers="d0e83 ">Timestamp<sup>1</sup></th>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e169 d0e85 ">Not valid</td>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e169 d0e87 ">Valid<sup>2</sup></td>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e169 d0e89 ">Not valid</td>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e169 d0e93 ">Not valid</td>
|
||
<td align="left" valign="top" width="16.666666666666664%" headers="d0e169 d0e97 ">Valid</td>
|
||
</tr>
|
||
</tbody>
|
||
</table>
|
||
</div>
|
||
<div class="section"><p><sup>1</sup> Date, time, and timestamp data types can be represented
|
||
by fields and expressions, but not constants; however, character constants
|
||
can represent date, time, or timestamp values.</p>
|
||
<p><sup>2</sup> The character
|
||
field or constant must represent a valid date value if compared to a date
|
||
data type, a valid time value if compared to a time data type, or a valid
|
||
timestamp value if compared to a timestamp data type.</p>
|
||
<p>The performance
|
||
of record selection can be greatly enhanced if a file on the system uses the
|
||
field being selected in a keyed sequence access path. This allows the system
|
||
to quickly access only the records that meet the selection values. If no such
|
||
access path exists, the system must read every record to determine if it meets
|
||
the selection values.</p>
|
||
</div>
|
||
<div class="section"><p>Even if an access path exists on the field you want to select
|
||
from, the system might not use the access path. For example, if it is faster
|
||
for the system to process the data in arrival sequence, it will do so.</p>
|
||
</div>
|
||
</div>
|
||
<div>
|
||
<div class="familylinks">
|
||
<div class="parentlink"><strong>Parent topic:</strong> <a href="rbafodynsrsex.htm" title="These topics provide examples of selecting records using the Open Query File (OPNQRYF) command.">Select records using the Open Query File (OPNQRYF) command</a></div>
|
||
</div>
|
||
<div class="relconcepts"><strong>Related concepts</strong><br />
|
||
<div><a href="rbafoappdbcs.htm" title="These topics describe double-byte character set (DBCS) considerations as they apply to the database on the iSeries system.">Double-byte character set considerations</a></div>
|
||
<div><a href="rbafoqryprf.htm" title="Here are tips and techniques for optimizing the performance when you use the Open Query File (OPNQRYF) command.">Open Query File (OPNQRYF) command: Performance considerations</a></div>
|
||
</div>
|
||
</div>
|
||
</body>
|
||
</html> |