CASE in Query

From MidrangeWiki
Jump to: navigation, search

This is about CASE as in Upper or LOWER CASE.

Also see [IT Jungle http://www.itjungle.com/fhg/fhg062205-story02.html] how-to article on this topic.

Sometimes we have data in fields with upper and lower case, and we want to ignore the case when it comes to accessing or sequencing the data.

In WRKQRY main definition options, select # 6 Collating Sequence, then on the next panel, select System Sort Sequence. Leave the 2 lines on bottom blank & press enter.

This gets us to a new screen defaulted to *JOBRUN on bottom and we put option 3 Shared on the field up top.

  • JOBRUN is the basis of SORT and LANGUAGE. The above combination of selections means CASE can be ignored for all Query criteria.

On WRKQRY main definition options, select bottom Processing Options. First screen is Specify Processing Options. Bottom of that should be Y to use collating sequence for all whatever.

An example of using this in selection criteria. Select records where CUSTNAM LIKE '%smith%' would catch all cases of smith regardless of SMITH Smith sMitH.