iSeries Query: Ignoring Field Case When Selecting Records

he Select Collating Sequence option on the iSeries Query “Define the Query” panel offers an easy and flexible way to specify shared sort sequence, which ensures that mixed-case character fields are sorted without regard for case (i.e., MR. SMITH and Mr. Smith are ranked equally).

First, select option 5 (System Sort Sequence) on the Select Collating Sequence panel. Then select option 3 (Shared) and Language ID: *JOBRUN.

The value *JOBRUN is resolved at runtime. Query will use this value to select the shared sort table based on the job’s sort sequence (SRTSEQ) and language ID (LANGID).

Using shared sort sequence also lets you ignore case when selecting records based on the value of character fields. Simply open the Specify Processing Options panel and type Y at the “Use collating sequence for all character comparisons” option.

To catch all customers named “smith” regardless of each letter’s case, select customer name “smith” (CUSNAM LIKE ‘%smith%’). That way, “Smith,” “SMITH,” and even “sMitH” will all appear in the report.

Read the published article

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s