IBM ISERIES SC41-5210-04 User Manual

iSeriesQuery for iSeries UseVersion 5SC41-5210-04ERserver

Running a Query for iSeries query...251Running a default query in Query for iSeries ...251Selecting records at

After making new selections or changing old ones, press the Enter key to rearrange them on the display inthe new sequence. You can then renumber them

Select and Sequence FieldsType sequence number (0-9999) for the names of up to 500 fields toappear in the report, press Enter.Seq Field Seq Field10 CO

After you have confirmed the fields you want by pressing the Enter key a second time without making anychanges to your sequence choices, Query complet

Chapter 7. Selecting records in Query for iSeriesThis chapter describes how you can perform record selection tests so that your query output contains

Select RecordsType comparisons, press Enter. Specify OR to start each new group.Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT...AND/OR F

Comparison values in Query for iSeriesThe value you compare the field with can be:v Another fieldv A numeric constantv A character constant (SBCS or D

SBCS characterTimestampDBCS-eitherDBCS-openv Any field can be compared to the keyword NULL using the IS or ISNOT test to determine if a field valueis

Note: If you are sending queries between countries that use the comma for a decimal separator, put ablank after each comma separating the offset and l

Date, time, or timestamp comparisons in Query for iSeriesA date, time, or timestamp value may be compared either with another value of the same data t

Records are selected if the data in the field BALDUE is less than or equal to the data in the field CRLIMIT.When you test for RANGE, the contents of t

About Query for iSeries UseQuery for iSeries is a decision support utility you can use to obtain information from the DB2 UDB foriSeries. This book de

v Must be date constants if comparing to a date field. If the list contains correct representations of a datebut in other than SAA format, you may get

You can use some special characters to represent the positions in the field that you do not care about:v An underscore (_) means skip one character at

DBCS-only LIKE, NLIKE (not like) pattern in Query for iSeries: This pattern, which contains onlydouble-byte characters, can be used for any bracketed-

If the test pattern consists of two apostrophes alone (’’ or G’’) or two apostrophes enclosing only DBCSshift-out and shift-in characters (’s/os/i’ or

Select RecordsType comparisons, press Enter. Specify OR to start each new group.Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT...AND/OR F

Select RecordsType comparisons, press Enter. Specify OR to start each new group.Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT...AND/OR F

104 Query for iSeries Use V5R2

Chapter 8. Selecting sort fields in Query for iSeriesThis chapter describes how you can select sort fields to control the arrangement of the data in y

Select Sort FieldsType sort priority (0-999) and A (Ascending) or D (Descending) forthe names of up to 32 fields, press Enter.SortPrty A/D Field___ _

v The states, and the customers listed for each state, should appear alphabetically.In order to obtain the order you want, your first sort field is cr

x Query for iSeries Use V5R2

Select Sort FieldsType sort priority (0-999) and A (Ascending) or D (Descending) for thenames of up to 32 fields, press Enter.SortPrty A/D Field Text

SBCS character data in SBCS, DBCS-open, and DBCS-either fields are sorted in the order of thehexadecimal representation of the characters or an order

110 Query for iSeries Use V5R2

Chapter 9. Selecting a collating sequence in Query for iSeriesThis chapter describes how you select the collating sequence for your query. The collati

To collate means to place items in proper sequence or to check that items are in proper sequence. ForQuery for iSeries purposes, collating sequences a

Selecting a Query for iSeries collating sequenceTo select a collating sequence, you have to understand how the data exists in your files. For example,

Table 3. Example of How a Code Table Works (continued). The value in the left column is the first half of thehexadecimal value. The value at the top o

as in most Query for iSeries language sequences, each pair of uppercase and lowercase letters (such asA and a) sort together because they share a coll

The CCSID of the sequence is shown at the top and is always your job CCSID. If the collating sequenceCCSID does not match your job CCSID, the previous

Select Translation TableLibrary ... *LIBL Name, *LIBL, F4 for listSubset ... __________ Name, generic*Position to ... __________ Starting

Part 1. Introduction to Query for iSeriesChapter 1. What is Query for iSeries? ...3Query for iSeries fundamentals ...

Select System Sort SequenceType choices, press Enter.Sort Sequence...1 1=Job run2=Unique3=SharedLanguage id...*JOBRUN *JOBRUN, language id, F4 fo

Chapter 10. Specifying report column formatting in Query foriSeriesThe first part of this chapter describes how you control the format of your query o

Each column heading appears in the report exactly as you type it. Each heading can be up to three20-character lines, and you can use any characters yo

v 0 through 9 (single-precision) or 0 through 17 (double-precision) for floating-point fieldsFor date, time, timestamp and DBCS fields, you cannot cha

Note: You cannot change the order of the fields on your report during the formatting process. If youdetermine that you need to change the order of the

The Define Numeric Field Editing display allows you to specify the type of editing you want to use to edit anumeric field. The editing determines how

v The Edit option value is the value last specified in the query definition, the value associated with thefield definition in the file definition (if

Decimal point in Query for iSeries reportsType the option number of the character that you want used as the decimal point in this field. The optionnum

v If you specify option 3 (Floating currency symbol), then the negative sign moves (or floats), dependingon how many leading zeros it replaces, so tha

The right currency symbol is always in a fixed position.Print zero value in Query for iSeries reportsWhen this field contains a numeric value of zero,

2 Query for iSeries Use V5R2

The Describe Date/Time Field Editing display allows you to specify the type of separator character thatyou want used for a numeric field that contains

Specifying an edit code in Query for iSeries reportsThe Specify Edit Code display allows you to specify which edit code or user-defined edit descripti

Note: For all these edit codes, decimal points are always shown, and leading zeros are alwayssuppressed.Query uses the J edit code as the default edit

Optional edit code modifier in Query for iSeries reportsType the character that you want used as the optional modifier for the specified edit code. If

Specify Edit WordField...: Heading1...:Length ...: Heading2...:Decimal...: Heading3...:Type information, press Enter. (Put quotes around edit w

v An edit word does not have to have a decimal digit position (to the right of the decimal point) for eachdecimal digit in the field; only the total l

134 Query for iSeries Use V5R2

Chapter 11. Specifying report summary functions in Query foriSeries reportsThis chapter describes how to specify the summary functions for each of the

2=AverageShows the average of the values in the field for the break level or for the whole column (numeric fieldsonly). The average is the total divid

Summary functions can be specified for as many of these fields as you want, as long as that type offunction is valid for that type of field.Summarizin

Chapter 1. What is Query for iSeries?Query for iSeries is an IBM®licensed program and a decision support utility that can be used to obtaininformation

138 Query for iSeries Use V5R2

Chapter 12. Defining Query for iSeries report breaksThis chapter describes how you define the report breaks that you want to occur in your query repor

Define Report BreaksType break level (1-6) for up to 9 field names, press Enter.(Use as many fields as needed for each break level.)Break SortLevel Pr

v If no break fields are selected, no summary functions are selected and final summaries are suppressed.Only a count of the records is produced.In a n

You can exclude break fields from showing in the report in column format and still use their values in thereport’s break text. That is, if you specify

Defining report break formatting in Query for iSeries reportsThe Format Report Break display is used to format each of the break levels (1 through 6)

N=NoAny summary values are not to be suppressed. They are to be included each time that a report breakoccurs at this break level.Y=YesAll summary valu

Chapter 13. Selecting output type and output form in Queryfor iSeries reportsThis chapter describes how you select where you want your query output to

output definition information. If no field selections are made, Query produces all fields for a databasefile. Result fields are ordered last instead o

N=NoThe data for each output record is not to be wrapped when the length of the output record exceedsthe width of the report line. Instead, any extra

Another record format, named ZIPADDRESS, might be defined for a file that might be location-oriented andcontain only the ZIPCODE, STATE, CITY, and STR

Original report:Position to line ... Shift to column . . .Line ...+...1...+...2...+...3...+...4...+...5...+..INTEGER CHAR DECIMAL NUMERIC

Define Printer OutputType choices, press Enter.Printer device... *PRINT *PRINT, nameForm sizeLength... ___ Blank, 1-255Width ... 132 Blank, 1

If you want to increase the left margin of the report (with the left edge being print position 1), go to theSpecify Report Column Formatting display a

Completing this display does not cause the query to immediately run and print a report. You must still usethe Exit This Query display (see Chapter 15,

v If you type *STD, the system assumes that the standard forms are already in the printer, and nomessage is sent to the system operator.Copies in Quer

N=NoNo cover page is to be printed for your report. If you specify text on the Cover page title prompt, it issaved for future use.Cover page title of

Print standard page headings in Query for iSeries reports: Type a Y or N to indicate whether thesystem’s standard headings are to be printed on each p

For database file output, some report characteristics (although they may be defined in this query definition)are ignored. If you later change the outp

Note: This can cause an unwanted result if a date value outside the range 1940 through 2039 is put intoa file field that has a two-digit year format.

definition is the only way to determine some of the assigned attributes, such as the allocated length forvariable length fields. Changing the format d

v CL and DDS use the same terms and descriptions as IDDU, but they provide additional support for files.Using DDS and CL commands, you can define and

If you specify *FILE, *FIRST, or *LAST in this prompt, a member is created with the same name as the fileif the file is created or replaced or has no

For all options, if the file does not exist in the specified library, Query tries to create that file.Authority in Query for iSeries reports: Type the

Summary-only output of a Query for iSeries report to a database fileThe data sent to a database file, when summary-only output is selected, is one of

Break level ID (1) | | | ||||| ||Overflow indicator (1) Total salary (11.2) ||| ||County (11) | Average salary (8.2)||City (10) Count (7.0)The number

162 Query for iSeries Use V5R2

Chapter 14. Specifying Query for iSeries processing optionsThis chapter describes how you specify processing options for running a query. The processi

Rounding numeric field values during Query for iSeries processingYou can control whether the result of numeric field calculations or field length chan

Whether you choose to have decimal data errors ignored depends on whether your data contains valuesthat cause decimal data errors. You may want to def

166 Query for iSeries Use V5R2

Chapter 15. Exiting and running a Query for iSeries queryThis chapter describes how to exit query definition and the different ways you can run your q

Figure 3. Major Tasks on the Query Menu and the Work with Queries Display6 Query for iSeries Use V5R2

Describing the Query for iSeries query definitionYou can also type some descriptive text about the query. The text reminds you what the query is for w

Running a Query for iSeries queryRunning a query consists of telling Query for iSeries to use the query definition to acquire the data youwant from se

You can run a query and get the report without saving the definition of the query only if you are runninginteractively. (The query must be saved to ru

Work with QueriesType choices, press Enter.Option ... _ 1=Create 2=Change 3=Copy 4=Delete5=Display 6=Print definition8=Run in batch 9=RunQuery ...

Following are some suggestions for using the RUNQRY command:v If you want to use an existing query without changing the file or files to query, specif

Chapter 16. Working with Query for iSeries query definitionsThis chapter describes the ways you can work with your query once it has been created and

Define the QueryQuery...: QRY1 Option ...: ChangeLibrary...: QGPL CCSID ...: 37Type options, press Enter. Press F21 to select all.1=SelectOpt

When you finish making changes on all the displays you want to change, Query returns you to the Definethe Query display, where the options now defined

Considerations for changing Query for iSeries queriesTo make changes to your query, choose the correct options on the Define the Query display. Refer

Table 8. Effect of Collating Sequence and Processing Option on Selection. Either or both NAME1 and NAME2 areDBCS data. Yes means the record under test

Chapter 2. General operating information for Query for iSeriesThis chapter describes the general operating information for Query, such as getting star

To place the copy in the same library as the original, do not type anything in the To library prompt.Then select a choice in the Replace query prompt:

delete. (For more information about using lists, see “Using lists in Query for iSeries” on page 9.) Thenpress the Enter key. The Confirm Delete of Que

If you display a query and decide you want to change it, you have to return to the Work with Queriesdisplay and use option 2 (Change). You can print t

When you select option 6 (Print definition) on the Work with Queries display and press the Enter key, thequery definition is printed immediately.Infor

Example of printed record format information for a Query for iSeriesquery definitionThe following is an example of one particular part of the query de

E means DBCS-eitherG means DBCS-graphicL means DateT means TimeZ means Timestampv The Null column shows whether the field is null capable (Y) or not (

184 Query for iSeries Use V5R2

Part 3. Advanced information about Query for iSeriesChapter 17. Additional information about Query for iSeries for programmers ...187Files with d

186 Query for iSeries Use V5R2

Chapter 17. Additional information about Query for iSeries forprogrammersThis chapter provides additional information which may be of interest to prog

Using Query for iSeries function keysFunction keys like Enter, Help, and Print can be used at any time with any query display. However, not allthe fun

Joining files in Query for iSeriesFor joining files (type of join):v Type 1 join is the same as for Query/36 (on System/36).v Type 2 join selects all

In calculations involving many large fields, you can control your results better by breaking the expressioninto parts, calculating each part as a sepa

Example: Increasing the decimal precision for result fields in Query foriSeriesFigure 5 has a result field with a current maximum precision of 31 (len

Selecting records (ignoring field case) in Query for iSeriesTo select records ignoring the case, do one of the following:v Define your own collating s

192 Query for iSeries Use V5R2

Part 4. Appendixes© Copyright IBM Corp. 2000, 2002 193

194 Query for iSeries Use V5R2

Appendix A. Differences between Query for iSeries andQuery/36This appendix describes the differences between Query for iSeries and Query/36. The Query

spacing. In Query for iSeries, the report skips an extra line for report breaks unless it is defined toskip to a new page. This means the first detail

Command differences between System/36 and Query for iSeriescommandsThe following table lists the System/36 Query commands and the equivalent OS/400 sy

DBCS characters take twice the amount of space of SBCS characters. In addition, a shift-out characterprecedes DBCS data, and a shift-in character foll

198 Query for iSeries Use V5R2

Appendix B. Practice exercise for Query for iSeries queryTo do an exercise using query, you must first have a file set up with some data. This exercis

7. You will name the file definition first, so select option 3 (File). (The name SYSDIC appears in the Datadictionary prompt on the following display;

Create and Select Field DefinitionsDefinition...: NAMEADDRR Dictionary...: SYSDICPosition to... .__________ Field, sequence (0-99999)Type seq

Create Field DefinitionsType information, press Enter to create.Field type (size)..: 1=Character (1-32766)2=Numeric (1-31, decimal positions 0-31)3=DB

you press F3 (Exit) without making any changes to the display.You are finished creating the definitions describing a database file. The instructions t

1. Choose option 3 (Work with database files) from the IDDU menu (or type WRKDBFIDD on a commandline).2. On the Work with Database Files display, spec

WORK WITH DATA IN A FILE Mode...: ENTRYFormat...: NAMEADDR File...: NAMEADDRLASTNAME: SIMPSONFIRSTNAME: FRANKADDRESS1: 1722 ORANGE STREETADDRESS2:C

End Data EntryNumber of records processedAdded ...: 10Changed ...: 0Deleted ...: 0Type choice, press Enter.End data entry ... Y Y=Yes, N=NoYou

v AmountAfter you create and save the query, you run it as it exists and then change the query and run it again.Query for iSeries query exercise: Crea

The number of items shown in the list part of the display varies, depending on what form the list is in andthe amount of space used by the prompt part

Notice that Query has already supplied a 1 for the Specify file selections option on this display. Thisis because whenever you create a query, you mus

Specify File SelectionsType choices, press Enter. Press F9 to specify an additionalfile selection.File... NAMEADDR Name, F4 for listLibrary ...

Exit This QueryType choices, press Enter.Save definition . . . Y Y=Yes, N=NoRun option... 1 1=Run interactively2=Run in batch3=Do not runFor a save

Work with QueriesType choices, press Enter.Option ... _ 1=Create, 2=Change, 3=Copy, 4=Delete5=Display, 6=Print definition8=Run in batch 9=RunQuery

Define the QueryQuery...: KJOQRY Option ...: CHANGELibrary...: YOURLIB CCSID...: 37Type options, press Enter. Press F21 to select all.1=Sele

Select RecordsType comparisons, press Enter. Specify OR to start each new group.Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT...AND/OR F

Display ReportReport width...: 117Position to line ... ______ Shift to column ... _____Line ...+...1...+...2...+...3...+...4...+...5.

Exit This QueryType choices, press Enter.Save definition . . . N Y=Yes, N=NoRun option... 3 1=Run interactively2=Run in batch3=Do not runFor a save

1. On the command line of the OS/400 Main Menu, type the DSPOBJD command as shown on thefollowing display and press the Enter key. This creates the fi

Work with QueriesType choices, press Enter.Option ... 1 1=Create, 2=Change, 3=Copy, 4=Delete5=Display, 6=Print definition8=Run in batch 9=RunQuery

Using Query for iSeries commandsA command is a statement used to request a function of the system. This means you need onlyremember a command that is

Specify File SelectionsType choices, press Enter. Press F9 to specify an additionalfile selection.File... QRYFILE Name, F4 for listLibrary ...

Select and Sequence FieldsType sequence number (0-9999) for the names of up to 500 fields toappear in the report, press Enter.Seq Field Seq Field Seq

Select and Sequence FieldsType sequence number (0-9999) for the names of up to 500 fields toappear in the report, press Enter.Seq Field Text Len Dec1

Select RecordsType comparisons, press Enter. Specify OR to start each new group.Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT...AND/OR F

Select RecordsType comparisons, press Enter. Specify OR to start each new group.Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT...AND/OR F

Select Sort FieldsType sort priority (0-999) and A (Ascending) or D (Descending) forthe names of up to 32 fields, press Enter.SortPrty A/D Field Text

Specify Report Column FormattingType information, press Enter.Column headings: *NONE, aligned text linesColumnField Spacing Column Headings Len Dec Ed

Select Report Summary FunctionsType options, press Enter.1=Total 2=Average 3=Minimum 4=Maximum 5=Count---Options--- Field Text Len DecODLBNM LIBRARY 1

Define Report BreaksType break level (1-6) for up to 9 field names, press Enter.(Use as many fields as needed for each break level.)Break SortLevel Pr

Format Report BreakBreak level ...: 1Type choices, press Enter.(Type &field in text to have break values inserted.)Skip to new page... N Y=Y

If you select option 3 (Delete a query) on the Query Utilities menu, you are shown the prompt display forthe Delete Query (DLTQRY) command. You can us

Display ReportReport width...: 104Position to line ... Shift to column ...Line ...+...1...+...2...+...3...+...4...+...5...+...6...

Exit This QueryType choices, press Enter.Save definition . . . N Y=Yes, N=NoRun option... 3 1=Run interactively2=Run in batch3=Do not runFor a save

230 Query for iSeries Use V5R2

Appendix C. Query for iSeries performance tips andtechniquesThis appendix provides guidelines for improving the performance of the Query for iSeries p

Note: Having existing access paths is important because a temporary access path created by Query isnot saved. It must be created each time that partic

Each time you run a saved query, Query validates the access plan by checking that the files and accesspaths named in the plan still exist.If it is val

stored in the low-order four bits of each byte. The high-order four bits of the low-order byte contain thesign. The high-order four bits of all other

retrieve the text for each field. Also, showing the text for each field results in fewer fields being shown oneach display so you have to page through

Note: If there is no existing access path matching some of the record selection tests, Query does notbuild an access path solely for purposes of selec

input or original file name. This produces a new database file containing the sorted records. The timerequired to produce the sorted records may vary,

9=Run Runs a query. Query displays a report, prints a report, or puts the data into a databasefile, depending on what is specified in the query defini

However, if the data you want to view is at the end of the searched records, it may take longer thananticipated to display those records. This may als

Option 2—Matched records with primary fileA record from the primary file is selected regardless of whether there is a match with any of thesecondary f

Be careful with using the “NE” comparison between fields from different files on the Specify How to JoinFiles display. This could result in a large nu

Query for iSeries performance tuningA properly tuned system provides much better overall performance than one in which performance tuninghas not been

display and also the total amount of time it takes the query to run. If a significant portion of thequery run time is spent building an access path, a

Appendix D. Preventing users from running Query for iSeriesqueries interactivelyThe system administrator can restrict users from running queries inter

244 Query for iSeries Use V5R2

Appendix E. Coded character set identifiers (CCSIDs) inQuery for iSeriesThis appendix contains information about coded character set identifiers or CC

Query for iSeries recognizes when conversion of data, collating sequences, and text is needed, andperforms the conversion. The system notifies you if

CCSID marking in Query for iSeriesThe following bulleted objects contain CCSID tags used by query processing. Each item inherits its CCSIDfrom the con

You can select as many as 30 queries from the list, as well as type a query name and option in the firstlist position of this display. Query processes

- Text about the member *- Data (the CCSID tag or tags are in the format definition)Notes:1. The CCSID of the query definition is shown on displays th

Note: The query profile, user-defined collating sequence default, and CCSID are only updated if youpress F23 from the Define Collating Sequence displa

Table 10. How CCSIDs Affect Displaying a QueryJob CCSID Query CCSIDConstants Processed as Markedwith CCSID37 37 3765535 37 3737 65535 6553565535 No ta

For example, the language sequence is used to initialize the Define Collating Sequence display if there isno previously defined sequence for the query

Table 12. How CCSIDs Affect Run-Time Record SelectionJob CCSID Query CCSID Constants Treated as CCSID37 37 3765535 37 3737 65535 3765535 No tag 655353

CCSID and result field column headings in Query for iSeriesA column heading in a result field definition is converted, if necessary, to the job or doc

CCSID compatibility considerations in Query for iSeriesCCSID marking makes it possible for conversions to be performed before presentation of marked m

Table 13. CCSIDs Not Compatible-Definition-Time Consequences (continued)Item 1 Item 2 ConsequenceCollating sequence Field Cannot perform comparison fo

Table 14. CCSIDs Conversion Problems-Run-Time Consequences (continued)Item 1 Item 2 ConsequenceCollating sequence Job Cannot convert table for perform

Table 16. How CCSIDs Affect Query UseJob CCSID Query CCSIDRun QueryallowedChange QueryallowedRun time recordselectionDisplay QueryallowedA A YesYesYes

library group are shown. For example, if you typed IN* in the Subset prompt, you might see a list of querynames like: INTEREST, INTFEB, INTMARCH, INVE

258 Query for iSeries Use V5R2

BibliographyThe following OS/400 books contain informationyou may need. The books are listed with their fulltitle and base order number.v ADTS/400: Da

260 Query for iSeries Use V5R2

NoticesThis information was developed for products and services offered in the U.S.A. IBM may not offer theproducts, services, or features discussed i

The licensed program described in this information and all licensed material available for it are provided byIBM under terms of the IBM Customer Agree

IndexSpecial Characters*ALL authority 159, 168*ALL join 44, 188*ALL special library name 10*ALLUSR special library name 10*CHANGE authority 159, 168*C

CCSID (coded character set identifier) (continued)definition 246displaying 17displaying query 249edit word 132file selection 252footing 253join tests

coded character set identifier (CCSID) (continued)sort field 253summary function 253translation table 116collating sequence 87, 111, 112, 249affecting

constant (continued)graphicmigration 241numericrecord selection test 94result field 59control language (CL) 5creating database files 5Copy Queries dis

date, time, and timestamp field (continued)migratingNtoN-1 241date, time, and timestamp valueaverage summary function 136comparing to null 96IS, ISNOT

Work with QueriesLibrary... QGPL Name, *LIBL, F4 for listSubset ... __________ Name, generic*Position to... __________ Starting character

definition (continued)changing 30, 173changing (example) 211copying 177copying before changing 173creating 27, 30creating (example) 206deleting 179dis

dividing by zero (0)example 83DLTQRY (Delete Query) command 11, 178double-byte character set (DBCS)bracket characters 9character constantshift-in char

field (continued)length and decimal position in report 120missing from file definitionduring file selection process 55starting to change or display a

function (continued)HOURargument rules 76syntax diagram 76introduction 5MICROSECONDargument rules 77syntax diagram 77minimum summarynull value 135MINU

increasing the decimal precision 190information for programmers, advanced 187interactive data definition utility (IDDU)data dictionary 4definition 199

list (continued)selecting itemshow 10library name 10queries 10query name 10selecting items from 16library 14subsetWork with Queries display 14LIST (li

OobjectDB2 UDB for iSeries program 5query definition 5omitting field from report 121OO (double-byte O) 8operatorconcatenation (||) 60null value 60nume

query definition (continued)DLTQRY command 11, 178Query for iSeries Utilities menu 11Work with Queries display 178displaying 179exiting 167information

report (continued)summary function 237specifying 135types 135using F13 to display layout 8using F5 to display 8, 169restrictionmigration definitions 1

selecting (continued)items from list 10library for file selection 34matched recordsprimary file 48selected files 47members for file selectionSelect Me

Other considerations when you are trying to locate or use a particular Query foriSeries queryYou should be aware of other considerations when you are

symbolic characterDBCS (double-byte character set) 4system administrator guidelines 243system sort sequence 117Ttestdisplaying 53test patternDBCS LIKE

value lengthdate 120time 120timestamp 120VARCHAR function 63VARGRAPHIC function 65variable-length fieldrelease restrictions 241verifying choicecreatin

280 Query for iSeries Use V5R2

Printed in U.S.A.SC41-5210-04

iSeriesQuery for iSeries UseVersion 5SC41-5210-04ER s e r v e r

v Change or display a query from the Work with Queries display or use the run-time record selectionoption of the RUNQRY command, and the query was def

select only the option(s) that need to be changed and then, when you are shown the appropriate display,you need only type the new choices or change th

20 Query for iSeries Use V5R2

Part 2. Defining and using Query for iSeries query definitionsChapter 3. Creating a Query for iSeries query definition ...27Starting Que

Query for iSeries date ...67Query for iSeries time ...67Query for iSeries timestamp ...

Numeric constants as values in Query for iSeries...94Date, time, timestamp constants as values in Query for iSeries ...95Nul

Edit code in Query for iSeries reports ...129Optional edit code modifier in Query for iSeries reports ...131Specifying

Using function key F5 when running a Query for iSeries query ...169Running a Query for iSeries query from the Query for iSeries menu ...

26 Query for iSeries Use V5R2

Chapter 3. Creating a Query for iSeries query definitionThis chapter describes the process of creating a query (that is, defining a query definition o

NoteBefore using this information and the product it supports, be sure to read the information in“Notices” on page 261.Fifth Edition (September 2002)T

If you position the cursor on the Query prompt and press F4 (Prompt) to show a list, it contains the namesof all the queries that are in the library (

Selecting options for a Query for iSeries query definitionTo select options that you want to use from those listed in the Query Definition Option colu

You can specify one, several, or all of the options shown. To select all of them, press F21.Moving through the Query for iSeries definition displaysFo

Suggested sequence of tasks for creating Query for iSeries queriesThe following is a suggested sequence of tasks to create queries:1. Define result fi

32 Query for iSeries Use V5R2

Chapter 4. Specifying and selecting files for a Query foriSeries queryThis chapter describes how you select and use one or more database files that ar

Specify File SelectionsType choices, press Enter. Press F9 to specify an additionalfile selection.File... __________ Name, F4 for listLibrary .

v If you type a generic library name (in the form of ABC*) or special library name in this prompt and pressthe Enter key, that library group is search

Using file IDs for a Query for iSeries queryFile IDs are used when you select more than one file. Although Query assigns a file ID value to each files

v If you typed a file name and left blank any of the Library, Member,orFormat prompts for the filev If there is a problem with one of the values you t

ContentsAbout Query for iSeries Use ...ixWho should read the Query for iSeries Use book ...ixPart 1. Introd

Specify File Selections display, it is used as a subset value on this display, and only file names startingwith those generic characters are included

To return to the complete list of file names, blank out this prompt (or put an * in it) and press the Enterkey.To move (position) the list of file nam

Select MemberFile ID ...: T01File...: EXAMPFILE1Library ...: CUSTINVType option (and Member), press Enter.1=SelectOpt Member_ ___________ EXAMP

Select Record FormatFile ID ...: T01File...: EXAMPFILE1Library ...: CUSTINVType option (and Format), press Enter.1=SelectOpt Format_ __________

Display File SelectionsID File Library Member FormatT01 EXAMPFILE1 CUSTINV *FIRST EXAMPFILE1T02 EXAMPFILE2 CUSTINV *FIRST EXAMPFILE2BottomPress Enter

Types of joins in a Query for iSeries querySpecify Type of JoinType choice, press Enter.Type of join... 1 1=Matched records2=Matched records with

Note: See “CCSID and join tests in Query for iSeries” on page 252 for information on how CCSIDs canaffect your join selections.Specify How to Join Fil

v A field name must be preceded by a 1- to 3-character file identifier if that field name is used in morethan one file selected for the query.v You ca

- DBCS-either- DBCS-open– DBCS-either field with:- SBCS character- Date- Time- Timestamp- DBCS-either- DBCS-open- DBCS-only– DBCS-open field with:- SB

You can obtain several different results from a file join when you use different combinations of join testsand options on the Specify How to Join File

Selecting files on the Query for iSeries Select File display ...37Selecting file members on the Query for iSeries Select Member display

Example: Selecting matched records using a primary file in a Query for iSeriesqueryTypea2ifyouwant to include in the query output every record in the

In our example, the RESIDENTS file is still the primary file, so only its records that do not have a matchingsecondary record are included in the quer

Correct method:On the Specify File Selections display, type the CUSTOMER file first, the PURCHASE file second, and the ITEMfile third. Specify the fil

Incorrect method:On the Specify File Selections display, type the CUSTOMER file first, and specify the file ID as A. Type theITEM file second (file C)

Query performs the file join in two steps:Step 1: Join the first two files, A and C.Query joins the files in the order listed, starting with file A (C

every record in file C. Because there are three records in A and five records in C, the result is 15 records.The join tests are not used in this step.

The Field columns show the fields being used to join the files. Each field name can have two parts: thefile ID and the actual name of the field, separ

Handling missing fields during file selection process of a Query foriSeries queryWhile you were changing a query, or were defining a new query that al

problems caused by the missing fields. You must also ensure that none of the fields were used in breaktest values on the Format Report Break display.

Chapter 5. Defining result fields in Query for iSeriesThis chapter describes how you define result fields. They need to be defined in your query if th

Letting Query for iSeries select records ...91Selecting the records you want in Query for iSeries ...91Compariso

always appears below and to the right of the list to tell you where you are in the list. More... means thatthere are more items after, and possibly be

DAYDAYSMONTHYEARv A time expression performs an operation on a time. Time expressions can contain the followingoperators or functions:+ (Addition)− (S

When you do more than one calculation within a numeric expression, use parentheses to tell Query inwhat order to do the calculations and to make the e

For example, if a character constant ’Dr. ’ and a character field named LASTNAME containing the valueSmith are concatenated, the result is a field con

Example of a character field substring: If a character field named ALPHA containing the value ABCDEFGHI isused in SUBSTR(ALPHA,4,3), the result is a c

Note: If you are sending queries between countries that use the comma for a decimal point, put a blankafter each comma separating values in a list of

v If the first argument is UCS2 graphic data and the result is mixed data, the result length is (2.5*(n-1)) +4.The third argument, if specified, must

VARGRAPHIC Query for iSeries functionThe VARGRAPHIC scalar function provides a way to convert:v character data (SBCS and Mixed) to DBCS graphic.v char

The actual length of the result depends on the number of characters in the argument. Each character ofthe argument determines a character of the resul

Query for iSeries dateA date expression is a three-part value (year, month, and day) designating a point in time under theGregorian calendar, which is

Record on one page in Query for iSeries reports ...147Displaying wrapping widths in Query for iSeries reports ...147Defi

Valid formats for times are listed in Table 2. Each format is identified by name and includes an associatedabbreviation (for use by the CHAR function)

Displaying constants format in Query for iSeriesThe Display Constants Format display shows you what date or time format you must use if you specify ad

Subtraction rules are different from addition rules because a date, time, or timestamp value cannot besubtracted from a duration. Also, subtracting tw

then MONTH(RESULT) = 12 + MONTH(DATE1) - MONTH(DATE2).YEAR(DATE2) is then incremented by 1.YEAR(RESULT) = YEAR(DATE1) - YEAR(DATE2).For example, the r

selection on specific year, month, and day values. The marked (*) lines are necessary and a sample reportfollows. If you require rounding for fields i

Time arithmetic operation in Query for iSeriesTimes can be subtracted, incremented, or decremented. The result of subtracting one time (TIME2) fromano

Timestamp duration in Query for iSeriesA timestamp duration represents a number of years, months, days, hours, minutes, seconds, andmicroseconds expre

DATE Query for iSeries functionThe DATE function returns a date from a value. The form is: DATE ( expression ) The argument must be either a:v Tim

If the argument is a date duration or a timestamp duration, the result is the day part of the value, a binaryfield with a value between −99 and 99. A

The resulting value of HOUR would equal 12.MICROSECOND Query for iSeries functionThe MICROSECOND function returns the microsecond part of a value. The

Appendix A. Differences between Query for iSeries and Query/36 ...195Conceptual Differences ...195Operational Diff

MONTH Query for iSeries functionThe MONTH function returns the month part of a value. The form is: MONTH ( expression ) The argument must be eithe

TIME Query for iSeries functionThe TIME function returns a time from a value. The form is: TIME ( expression ) The argument must be either a:v Tim

RESULT(x) = TIMESTAMP(DATEFRNK,TIMEFRNK)YEAR Query for iSeries functionThe YEAR function returns a numeric representation of the year part of a value.

Converting date for output to a display or printer in Query for iSeriesYou can use the date functions to convert an input date field to a different fo

Define Result FieldsType definitions using field names or constants and operators, press Enter.Operators: +, -, *, /, SUBSTR, ||, DATE...Field Express

Define Result FieldsType definitions using field names or constants and operators, press Enter.Operators: +, -, *, /, SUBSTR, ||, DATE...Field Express

– If your expression performs a division operation, avoid dividing by zero by defining the denominatoras the result field just before the result field

Column headings in Query for iSeriesIf you want to specify a column heading to be used for a result field, type the heading you want on thethree lines

Example of defining a result field in Query for iSeriesAn example of how you would create a numeric result field called DAYS using the expression WEEK

Chapter 6. Selecting and sequencing fields in Query foriSeriesThis chapter describes how you select fields that you want to include in your query outp