IBM SC41-5210-04 manual Considerations for changing Query for iSeries queries

Considerations for changing Query for iSeries queries

To make changes to your query, choose the correct options on the Define the Query display. Refer to the appropriate chapters or appendixes in this guide for information and considerations on how to enter the definitions that you want. Each query is saved with a CCSID. You cannot change a query if your job CCSID is not compatible with the query CCSID (one of the CCSIDs is 65535 or they are the same).

Note: See “Changing a Query for iSeries query” on page 250 for more information on how CCSIDs affect processing a query.

Changing your collating sequence on Query for iSeries queries

A change in your collating sequence can affect join tests, record selection tests, and sorts involving SBCS data in SBCS, DBCS-open, and DBCS-either character fields. The extent of the effect depends on the test.

Changing the setting of the Use Collating Sequence processing option can change your results. If you set the processing option to Yes, the EQ, NE, LIST, NLIST, LIKE, and NLIKE tests compare character collating values. These tests compare the hexadecimal character codes if you set the option to No.

Note: The Use Collating Sequence option does not affect your output when the collating sequence is set to *HEX.

LT, LE, GT, GE, and RANGE tests compare collating sequence values regardless of how you set the processing option. A change to your collating sequence can change the records that these tests select. A change to the collating sequence can also affect your query’s minimum and maximum summary values, the location of report breaks, and the order of selected records (if you use a field containing SBCS character data for sorting).

For example, you have 2 collating sequences. In one, the character E has a higher sequence number than e, and in the other, the two characters have equal (shared) sequence numbers. In one record, field NAME1 has a value of LEE, and field NAME2 has a value of Lee. Table 7 and Table 8 show the results of comparing the strings (NAME1 and NAME2) using various tests for a collating sequence with:

vUnique weights used in all tests

vShared weights used in all tests

vUnique weights not used in EQ and NE tests

vShared weights not used in EQ and NE tests

Table 7 applies when all data is SBCS. Table 8 applies when some or all of the data is DBCS.

Table 7. Effect of Collating Sequence and Processing Option on Selection. Both NAME1 and NAME2 are SBCS data. Yes means the record under test is selected. No means it is not selected.

	ColSeq=YES Unique	ColSeq=YES Shared	ColSeq=NO Unique	ColSeq=NO Shared
Test	Weights	Weights	Weights	Weights
NAME1 EQ NAME2	No	Yes	No	No
NAME1 GE NAME2	Yes	Yes	Yes	Yes
NAME1 GT NAME2	Yes	No	Yes	No
NAME1 NE NAME2	Yes	No	Yes	Yes
NAME1 LE NAME2	No	Yes	No	Yes
NAME1 LT NAME2	No	No	No	No

176Query for iSeries Use V5R2