Skip to content

Open Query File Magic! A Complete Guide to Maximizing the Power of OPNQRYF

Best in textbook rentals since 2012!

ISBN-10: 1883884578

ISBN-13: 9781883884574

Edition: 2nd 1998

Authors: Ted Holt

List price: $69.00
Blue ribbon 30 day, 100% satisfaction guarantee!
Out of stock
We're sorry. This item is currently unavailable.
what's this?
Rush Rewards U
Members Receive:
Carrot Coin icon
XP icon
You have reached 400 XP and carrot coins. That is the daily max!


This time-saving book shows you how to exploit the power of IBM's Open Query File (OPNQRYF) to sort, select, summarize, and join data for input to programs. With OPNQRYF, you can allow end users to see data sorted the way they want it, to see only the data fields they are interested in, to calculate new fields, or see data in summary form--all without having to write high-level language programs. The book covers keywords, special parameters, record selection, assigning values to fields, joining two or more files, and much more.
Customers also bought

Book details

List price: $69.00
Edition: 2nd
Copyright year: 1998
Publisher: MC Press, LLC
Publication date: 12/1/1998
Binding: Paperback
Pages: 302
Size: 7.25" wide x 9.00" long x 0.75" tall
Weight: 1.034
Language: English

How Opnqryf Works
Open Data Paths and Pointers
Comparing Opnqryf to Logical Files
Coding Opnqryf
CL Requirements
High-Level Language Requirements
The Keyfld Parameter
Valid Values
Variable Key Field Name
Accessing an Opnqryf File Randomly
Special Sorting Problems
Qualified Field Names
Performance Considerations
Record Selection
The Basics
Using Literals
Relational Operators
Logical Operators
Arithmetic Operators
String Operators
Built-In Functions
Commonly Used Functions
%Max and %Min
Special Functions and Equal Conditions
Special Functions for Null-Capable Fields
Syntax Considerations
Priority of Operators
Using CL Variables
Using a Qryslt Variable
Modifying a Static Qryslt Variable
Record Selection in HLL Programs
Performance Considerations
Assigning Values to Fields
The Format Parameter
The Mapfld Parameter
Mapfld Techniques
Substituting a Value
Virtual Fields
Date Arithmetic with Numeric and Character Fields
Making One File Look Like Another File
Preventing Division by Zero
Variable Control Breaks
Basic CL Logic
Additional Basic CL Logic
Logical Functions
Other Functions
Joining Files
Join Parameters
The File Parameter
The Format Parameter
The Jfld Parameter
The Jorder Parameter
Resolving Ambiguity of Field Names
Join Types
The Inner Join
The Left Outer Join
The Exception Join
Joining on More Than One Field
Joining a File to Itself
Unequal Joins
Joining on Fields of Different Types and Formats
Joining Through an Intermediary File
Another Way to Do Date Arithmetic
The Cartesian Product
Using a Cartesian Product to Generate Percentage of Total
Joining In the Qryslt Parameter
Why Join In the Qryslt Parameter?
Joining More than Two Files
Performance Considerations
Inner Joins Versus Left Outer Joins
Access Paths
Duplicate Test Conditions
Key Fields from More Than One File
Date and Time Data Types
Date, Time, and Time-Stamp Functions
Constant Functions
Extraction Functions
Conversion Functions
Duration Functions
Date, Time, and Time-Stamp Arithmetic
Summary Processing
Unique Key Processing
Group Processing
Grouping by Common Field Values
Selecting Groups
Grpslt Versus Qryslt
Grouping into One Record
Random Access
Reusing an Open Data Path
Shared Open Data Paths
The Posdbf Command
Resetting the File Pointer from a High-Level Language Program
The Optimize Parameter
The Cpyfrmqryf Command
Copying to a File
Using Cpyfrmqryf in Recap Jobs
Using Cpyfrmqryf for Random Access Over Group Queries
Using Opnqryf in Interactive Programs
The Optimize Parameter
The Seqonly Parameter
Access Paths
Opnqryf in the Integrated Language Environment
Activation Groups
Conversion from OPM to ILE
Another Example
Using Opnqryf in CL Programs
Using Two CL Programs
Using One CL Program
Opnqryf and the Year 2000 Issue
Sorting Records
Sequencing Technique 1: Adjust the Year
Sequencing Technique 2: Generate a Century Digit
Sequencing Technique 3: Joining
Record Selection
Selection Technique 1: Convert Dates
Selection Technique 2: Alter the Boolean Logic
Other Parameters of the Opnqryf Command
Option (Open Option)
Alwcpydta (Allow Copy Data)
Igndecerr (Ignore Decimal Errors)
Opnid (Open ID)
Commit (Commitment Control)
Type (Open Type)
Dupkeychk (Check for Duplicate Keys)
Strtseq (Sort Sequence)
Langid (Language Identifier)
Optallap (Optimize All Access Paths)
Comparing Opnqryf to Fmtdta Data
Contrasting Opnqryf and Fmtdta
Advantages of Opnqryf
How Not to Convert Fmtdta to Opnqryf
Performance Considerations
Data Retrieval Methods
Dynamic Processing
Key Selection
Key Positioning
Sort Routine
The Job Log
Improving Performance
Additional Considerations
Common Runtime Errors
Errors Caused by Invalid Parameter Values (CPF9899)
Missing Operand in the Expression in the Qryslt Parameter
Missing Operator in the Expression in the Qryslt Parameter
Key Field xxxxx/yyyyy Not Valid for Record Format
Key Field xxxxx on the Format Parameter Not Found
Key Field xxxxx/yyyyy Not Allowed in the Record Format
Field xxxxx Found in More Than One Record Format
Character "x" Following String "xx" "Not Valid
Other Runtime Errors
Cpf4174 Opnid (xxxxx) for File yyyyy Already Exists
Logic Errors
Many Records
No Records Selected
Query Results May Differ From Intended Value of and1
The Report Has the Wrong Sequence
Specifying Variable-Control Breaks
Built-In Functions
Single-Record Functions
Other Numeric Functions
Functions Allowed Only in Qryslt and Grpslt Parameters
Grouping Functions
Generating a Date-Conversion File
Optimization Messages
CPI4321 (Access Path Built for File and4)
CPI4322 (Access Path Built from Keyed File and1)
CPI4325 (Temporary Result File Built for Query)
CPI4326 (File and1 Processed in Join Position and2)
CPI4327 (File and1 Processed in Join Position and1)
CPI4328 (Access Path %4 Used By Query)
CPI4329 (Arrival Sequence Access Used for File and1)
CPI432A (Query Optimizer Timed Out)
CPI432C (All Access Paths Considered for File and1)
CPI432D (Additional Access Path Reason Codes Used)