friedkiwi/ibm-information-center
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
master
ibm-information-center/dist/eclipse/plugins/i5OS.ic.dbp_5.4.0.1/rbafodynsrex1.htm

161 lines
11 KiB
HTML
Raw Permalink Normal View History

Add readme and dist folders
2024-04-02 14:02:31 +00:00
<?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 &amp;CHAR *CAT '" *EQ FIELDA')</pre>
or,
in reverse order: <pre>OPNQRYF FILE(FILEA) QRYSLT('FIELDA *EQ "' *CAT &amp;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(&amp;CHARNUM *CAT ' *EQ NUM')</pre>
or,
in reverse order: <pre>OPNQRYF FILE(FILEA) QRYSLT('NUM *EQ ' *CAT &amp;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>
Reference in New Issue Copy Permalink