Manuals / IBM / Computer Equipment / Server

IBM SC41-5210-04 manual Query for iSeries Use V5R2

Models: SC41-5210-04

1 294

Download 294 pages 6.42 Kb

Page 38

26Query for iSeries Use V5R2

Image 38

IBM SC41-5210-04 manual Query for iSeries Use V5R2

Contents

ERserver Page ERserver Fifth Edition September Contents Selecting records in Query for iSeries Defining result fields in Query for iSeriesSelecting and sequencing fields in Query for iSeries Specifying report column formatting in Query for iSeries Selecting sort fields in Query for iSeriesSelecting a collating sequence in Query for iSeries Defining Query for iSeries report breaksExiting and running a Query for iSeries query Part 3. Advanced information about Query for iSeriesSpecifying Query for iSeries processing options Working with Query for iSeries query definitionsAppendix B. Practice exercise for Query for iSeries query Viii Query for iSeries Use V5R2 About Query for iSeries Use Who should read the Query for iSeries Use bookQuery for iSeries Use V5R2 Part 1. Introduction to Query for iSeries Query for iSeries Use V5R2 Files, fields, and record formats in Query for iSeries What is Query for iSeries?Query for iSeries fundamentals StateDouble-byte character set Dbcs fields in Query for iSeries UCS2 level 1 character set support in Query for iSeriesMajor functions of Query for iSeries Query for iSeries definitionsLibraries in Query for iSeries Query for iSeries Use V5R2 General operating information for Query for iSeries Using Query for iSeries function keys Displaying a Query for iSeries list Using lists in Query for iSeriesPrinting what you see on your Query for iSeries display Special Library Name Description Selecting items in a Query for iSeries listSpecial library names in Query for iSeries Query Using Query for iSeries commandsUsing the Query Utilities menu Using the Work with Queries display Choosing a single Query for iSeries task=Run Choosing multiple Query for iSeries tasksSpecifying a Query for iSeries query and its library QgplQuery for iSeries Use V5R2 Positioning a list of Query for iSeries queries Acctrecfeb AcctpayfebAcctpaymar QRY1 F12=CANCELDisplaying the format of constants in Query for iSeries Qgpl CcsidChanging your Query for iSeries queries Handling Query for iSeries messages and errorsQuery for iSeries profile information General operating information for Query for iSeries Query for iSeries Use V5R2 Creating a Query for iSeries query definition Selecting and sequencing fields in Query for iSeries Selecting sort fields in Query for iSeries Defining Query for iSeries report breaks Working with Query for iSeries query definitions Query for iSeries Use V5R2 Creating a Query for iSeries query definition Starting Query for iSeries query definitionDefine the Query Option Create Library Qgpl Selecting options for a Query for iSeries query definition Selecting files, records, and fields for Query for iSeries Moving through the Query for iSeries definition displaysConsiderations for creating Query for iSeries queries Creating a Query for iSeries query definition Query for iSeries Use V5R2 Specifying and selecting files for a Query for iSeries query Specifying file selections for a Query for iSeries queryFirst Choosing a file for a Query for iSeries queryChoosing a library for a Query for iSeries query Selecting multiple files for a Query for iSeries query ExampfileRemoving a option for a Query for iSeries query Using file IDs for a Query for iSeries queryConfirming your options for a Query for iSeries query Selecting files on the Query for iSeries Select File display CustinvCustinv Custreljan CustCustinv Custrelfeb Custinv CustrelmarSpecifying and selecting files for a Query for iSeries query EXAMPMBR1 EXAMPMBR2 EXAMPMBR3 EXAMPFMT1 EXAMPFMT2 EXAMPFMT3 EXAMPFILE2 Custinv First Joining files in a Query for iSeries queryEXAMPFILE1 Custinv First How to join files in a Query for iSeries query Types of joins in a Query for iSeries queryZIP NBR Rules for joining files in a Query for iSeries queryNBR Name Phone Addr Specifying and selecting files for a Query for iSeries query Examples of joining files in a Query for iSeries query Specifying and selecting files for a Query for iSeries query Query for iSeries Use V5R2 Specifying and selecting files for a Query for iSeries query Correct method Incorrect method Query for iSeries Use V5R2 Displaying all join tests in a Query for iSeries query Handling missing fields in a Query for iSeries query Page Query for iSeries Use V5R2 Defining result fields in Query for iSeries Creating results fields in Query for iSeriesQuery for iSeries result field name Query for iSeries expressionsPage Query for iSeries character expressions Query for iSeries concatenation operationQuery for iSeries character functions Substr Query for iSeries functionDigits Query for iSeries function Value Query for iSeries functionVarchar Query for iSeries function Reschar Vargraphic Query for iSeries function Result Ccsid Description CharacterDate, time, and timestamp expressions in Query for iSeries Query for iSeries date Query for iSeries timeQuery for iSeries timestamp Format Name Abbreviation Time Format ExampleDisplaying constants format in Query for iSeries Date arithmetic operation in Query for iSeries Subtracting dates in Query for iSeriesSUBSTRCHARDATE,5,2’/’ Incrementing and decrementing dates in Query for iSeriesWorking with numeric dates in Query for iSeries SUBSTRCHARDATE,1,2’/’Mmddyy Qtemp YymmddTime arithmetic operation in Query for iSeries Durations in Query for iSeriesTimestamp duration in Query for iSeries Date, time, and timestamp functions in Query for iSeriesChar Query for iSeries function Date Query for iSeries function DAY Query for iSeries functionDays Query for iSeries function Hour Query for iSeries functionMicrosecond Query for iSeries function Minute Query for iSeries functionMonth Query for iSeries function Second Query for iSeries functionTime Query for iSeries function Timestamp Query for iSeries functionYear Query for iSeries function Converting date formats in Query for iSeriesMmddyy Yyddd Commands to run the query in example 4 are Odqysd NE Omcrmm EQ Omstat EQ ’50’ Odstat NE ’99’ Column headings in Query for iSeries Length and decimal positions in Query for iSeriesExample of defining a result field in Query for iSeries Adding or removing result fields in Query for iSeriesAcctnumber Pastdue Company Selecting and sequencing fields in Query for iSeriesLetting Query for iSeries select and sequence fields FirstnameAcctnumber Pastdue Company Company Baldue Acctnumber Lastname Streetaddr City State Zipcode Telenumber CrlimitQuery for iSeries Use V5R2 Selecting the records you want in Query for iSeries Selecting records in Query for iSeriesLetting Query for iSeries select records Comparison fields in Query for iSeries Comparison values in Query for iSeries Numeric constants as values in Query for iSeries Fields as values in Query for iSeriesCharacter constants as values in Query for iSeries Test Description Comparison tests in Query for iSeriesNull values in Query for iSeries Testing for equal EQ and not equal NE in Query for iSeries Date, time, or timestamp comparisons in Query for iSeriesTesting for values in a list List Nlist in Query for iSeries Itemno List Lastname ListLastname Like ’ANNE EVA’Example 1 DBCSFLD1 Like ’s/oDD%%HH%%s/i’ Using more than one comparison test in Query for iSeries Year Baldue AND/ORMonth List Year Adding and removing comparisons in Query for iSeries Year ListQuery for iSeries Use V5R2 Selecting sort fields in Query for iSeries 105Lastname Account number Additional sort considerations in Query for iSeries Sorted report looks like this Query for iSeries Use V5R2 Purpose of a collating sequence in Query for iSeries Selecting a collating sequence in Query for iSeriesLetting Query for iSeries select a collating sequence Collating sequence and CCSIDs in Query for iSeries How a collating sequence affects Query for iSeriesSelecting a Query for iSeries collating sequence Query for iSeries Use V5R2 Defining your own collating sequence in Query for iSeries Selecting a translation table in Query for iSeries Selecting a system sort sequence in Query for iSeries F12=Cancel F13=Layout F18=Files F23=Save as default Column spacing in Query for iSeries reports Specifying report column formatting in Query for iSeriesFormatting the columns of the Query for iSeriesquery report Column headings in Query for iSeries reportsStarttime Length and decimal positions in Query for iSeries reportsLastname Init Omitting fields from a Query for iSeries report Editing numeric fields in Query for iSeries reports Defining numeric field editing in Query for iSeries reports=Edit code =Numeric editing choices=Date or time editing choice =Edit wordF10=Process/previous F12=Cancel F13=Layout Show negative sign in Query for iSeries reports Decimal point in Query for iSeries reportsThousands separator in Query for iSeries reports Left negative sign in Query for iSeries reportsRight currency symbol in Query for iSeries reports Show currency symbol in Query for iSeries reportsLeft currency symbol in Query for iSeries reports Right negative sign in Query for iSeries reportsReplace with option in Query for iSeries reports Print zero value in Query for iSeries reportsReplace leading zeros in Query for iSeries reports Single leading zero in Query for iSeries reportsDate/time separator in Query for iSeries reports Specifying an edit code in Query for iSeries reports Edit code in Query for iSeries reportsInternal Edit word in Query for iSeries reports Specifying edit wordsOptional edit code modifier in Query for iSeries reports =Asterisk fillF13=Layout F16=Remove edit F18=Files Edit word for summary total in Query for iSeries reports Query for iSeries Use V5R2 Total Types of summary functions in Query for iSeries reports=Total MAX=Maximum =Average=Minimum =CountFinal Totals Summarizing columns in Query for iSeries reportsName Total DUE Query for iSeries Use V5R2 Defining Query for iSeries report breaks Defining a Query for iSeries report breakDivision RegionRecords in file Field Company Department Division Employee Region Skip to new page in Query for iSeries reportsSuppress summaries in Query for iSeries reports Break text in Query for iSeries reports 145 =Printer =Database file=Detail Form of output of Query for iSeries reportsLine wrapping in Query for iSeries reports =Summary onlyDisplaying wrapping widths in Query for iSeries reports Wrapping width in Query for iSeries reportsRecord on one page in Query for iSeries reports Decimal Numeric Defining output to the printer in Query for iSeries reportsInteger Form length Printer device in Query for iSeries reportsForm size in Query for iSeries reports Form widthLine spacing in Query for iSeries reports Start line in Query for iSeries reportsEnd line in Query for iSeries reports Print definition in Query for iSeries reportsDefine Spooled Output Specify Cover Heading Footing F3=Exit F5=Report =Yes Libcrtaut LIBCRTAUT, *CHANGE, *ALL EXCLUDE, *USE QqryoutFile Query for iSeries Use V5R2 Page =New member =New file=Replace file =Replace memberAuthorization list name ExcludeLos Angeles Arcadia Using an output database file created by Query for iSeries Query for iSeries Use V5R2 Specifying Query for iSeries processing options Purpose of Query for iSeries processing optionsQuery for iSeries Use V5R2 Specifying Query for iSeries processing options Query for iSeries Use V5R2 Saving a Query for iSeries query definition Exiting and running a Query for iSeries queryEnding a Query for iSeries query definition Storing the Query for iSeries query definitionDescribing the Query for iSeries query definition Giving authority to others to your Query for iSeries queryRunning a Query for iSeries query LibcrtautQuery for iSeries Use V5R2 Running a Query for iSeries query using the Runqry command Query for iSeries Use V5R2 Changes you can make to a Query for iSeries query Working with Query for iSeries query definitionsChanging a Query for iSeries query definition Define the Query Prompt Changed Effect Considerations for changing Query for iSeries queries Copying a Query for iSeries query definition Acctrecfeb Inventfeb MailaddrRenaming a Query for iSeries query definition Deleting a Query for iSeries query definitionDisplaying a Query for iSeries query definition Printing a Query for iSeries query definition Information printed for a Query for iSeries query definition Breaklvl Working with Query for iSeries query definitions Query for iSeries Use V5R2 Part 3. Advanced information about Query for iSeries Query for iSeries Use V5R2 Overriding database files in Query for iSeries Files with different record formats in Query for iSeriesFile sharing considerations in Query for iSeries Using *ALL in Query for iSeries Joining files in Query for iSeriesDivision in Query for iSeries Addition and subtraction in Query for iSeriesMultiplication in Query for iSeries PRN299A PRN299BSelecting records ignoring field case in Query for iSeries Changing Decimal PrecisionQuery for iSeries Use V5R2 Part 4. Appendixes Query for iSeries Use V5R2 Conceptual Differences Operational DifferencesSort sequence Result fields Library defaultCharacter fields Calculation resultsSystem/36 OS/400 System Query for iSeries Use V5R2 Appendix B. Practice exercise for Query for iSeries query IdduSysdic NameaddrZIP ADDRESS1ADDRESS2 AmountADDRESS1 ADDRESS2 City State ZIP Amount LIBCRTAUT, *ALL, *CHANGE Query for iSeries query exercise Creating a database fileQuery for iSeries query exercise Entering data USE, *EXCLUDELastname Firstname ADDRESS1 ADDRESS2 City State ZIP Amount Work with Data in a FileEntry Lastname Firstname ADDRESS1 ADDRESS2 City State ZIP Amount =Yes, N=No Yourlib Query for iSeries query exercise Creating a queryKjoqry Bottom NameaddrrCreate Yourlib CcsidDaye BEN Change LIBCRTAUT, *ALL, *CHANGE EXCLUDE, *USESimpson Frank APT CQuery for iSeries query exercise Changing a query Press the Enter key. The Select Records display is shown ChangePress F20 to display the right portion of the report Change LIBCRTAUT, *CHANGE, *ALL EXCLUDE, *USE ADDRESS2 CityAPT Query for iSeries query exercise Creating an advanced query CebqryMain === WrkqryQname Qryfile QlidobjdOddtim Odctim Oddcen OdccenOdddat Odcdat Odlbnm OdobowOddtim Display Time Odobnm Object Odobfr Attribute Size Odobtp ListLibrary Object CLPOdlbnm Library Odobtp Object Type Odobat Odobsz Object Size OdobtxOdlbnm Library Odobtp Object Type Odobat Attribute Odlbnm Name Library Odobtp Object Type Odobat Attribute Odobsz Object Size Odobtx Text Description3 4 Break text Qname Qgpl File Tapf Name ObjectQgpl File Dspf Qgpl Query for iSeries Use V5R2 Keyed sequence access paths in Query for iSeries Introduction to Query for iSeries query processingSelect/omit access paths in Query for iSeries Access plans in Query for iSeriesFile definitions in Query for iSeries Updating access plans in Query for iSeriesFile definitions and data in Query for iSeries File data considerations for Query for iSeries Defining queries for Query for iSeriesSelect records in Query for iSeries Define result fields in Query for iSeriesSelect and sequence fields in Query for iSeries Select sort fields in Query for iSeries Select output type and output form in Query for iSeries Select collating sequence in Query for iSeriesSpecify report summary functions in Query for iSeries Option 1-Matched records Using join operations in Query for iSeriesSpecify processing options in Query for iSeries Option 3-Unmatched records with primary file Performance tips for join operations in Query for iSeriesOption 2-Matched records with primary file Miscellaneous tips and techniques for Query for iSeries Batch processing for Query for iSeriesQuery for iSeries migration considerations N to N-1 Query for iSeries performance tuningQuery running. Building access path for file X in Y Query for iSeries status messagesQuery running. Creating copy of file X in Y Query for iSeries debug mode messagesQuery running. Building access path from file X in Y Query running. Sorting copy of file *N in *N243 Query for iSeries Use V5R2 245 Query for iSeries Use V5R2 Ccsid marking in Query for iSeries CCSIDs and collating sequences in Query for iSeries Query for iSeries language sequences Displaying a Query for iSeries queryDefining a Query for iSeries query Changing a Query for iSeries queryGetting a list of objects with text in Query for iSeries Defining a collating sequence in Query for iSeriesRunning a default query in Query for iSeries Selecting records at run time in Query for iSeriesCcsid and join tests in Query for iSeries CCSIDs and Query for iSeries query definition itemsCcsid and file selections in Query for iSeries Ccsid and result field expressions in Query for iSeriesCcsid and record selection tests in Query for iSeries Ccsid and result field column headings in Query for iSeriesCcsid and sort fields in Query for iSeries Ccsid and summary functions in Query for iSeriesCcsid compatibility considerations in Query for iSeries ConsequenceCCSIDs Conversion Problems-Run-Time Consequences For displayed output, the run request continues, but all How CCSIDs Affect Query Use Query for iSeries Use V5R2 Bibliography 259Query for iSeries Use V5R2 261 Trademarks DB2Index Special Characters DateCharacter substitution warning Command Currentdate ISO JIS Local Dec column data type Definition Display Example Field Function 189 List Heading Ccsid coded character set identifier 253 Defining Dbcs Like Report Sort Field Currenttime Currenttimezone ISO JIS Local Work with File Definitions display Iddu 202 Query for iSeries Use V5R2 Page SC41-5210-04