Difference between revisions of "CASE in Query"

From MidrangeWiki
Jump to: navigation, search
(before fancy symbols)
 
 
(3 intermediate revisions by one other user not shown)
Line 1: Line 1:
 
This is about CASE as in Upper or LOWER CASE.
 
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.
 
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.
+
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.
  
This gets us to a new screen defaulted to *JOBRUN on bottom and we put option 3 Shared on the field up top.
+
[[Category:Query]]

Latest revision as of 20:01, 21 August 2006

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.