====== With ======

 


 
==== Description ====
 
The filter keyword With is used with OpenList commands to generate a subset of rows to be processed.
 
==== Syntax ====
 
**With_Keyword** [Every] [CaseSens | CaseInsens] COLUMN //comparison// [//value// | Matches //pattern// | //range//]
 
==== Remarks ====
 
Use With in an OpenList command when you want to define which rows are to be selected for processing. The With clause will cause the OpenList command to examine each row in the table and match it against the criteria following the With keyword. If the row matches the criteria, it will be added to the list of rows to be processed. If the row does not meet the selection criteria, it will be ignored.

With does not actually create a table of the subset of rows selected. Instead, it creates a list of keys that represent the rows meeting specific criteria.
 

^Option^Description^
|With_Keyword|The with_keyword may be With, Without or any of the following keywords:\\ \\ __Keyword__ - __Example__\\ With - Because [It Has], Because [Its], That [Has], When [It Has], When [Its], If [It Has], If [Its], Whenever [It Has], Whenever [Its], Include, Including, Which [Has]\\ Without - Except If [It Has], Except If [Its], Unless [It Has], Unless [Its], Exclude, Excluding With|
|Every|Use Every when column is multi-valued. Only those rows are selected that meet the specified conditions for all values in column.|
|CaseSens|Use the CaseSens keyword to force a case-sensitive search of data.|
|CaseInsens|Use the CaseInsens keyword to force a case-insensitive search of data.|
|COLUMN|The column is the name of a column in the table being processed. If COLUMN is not found in the dictionary of the table being processed, OpenInsight will look in the dictionary of the MYTABLE table.|
|//comparison//|Use //comparison// to define how With compares each row. If //comparison// is not specified, the default value is "=" (equal). Below is a listing of the comparison operators:\\ \\ __Operator__ - __Description__\\ =  - Eq, Equal {TO}, That Is, Like, Are.\\ < - LT, Less Than, Before ,Under.\\ <= - LE, Less Than {Or} Equal To.\\ <> - NE, Not, Not Equal {To}, Doesnt, Arent, Isnt.\\ >= - GE, Greater Than {Or} Equal To, From.\\ > - GT, Greater Than, Later Than, After, Over.\\ ] - Starting {In}.\\ [ - Ending {In}.\\ [ ] - Containing Match, Matches.|

                
==== Examples ====

The comparison operators "[ ]" (for Containing), " ] " (for Starting With), and " [ " (for Ending With) can be used in a number of ways. For example, the following two commands are equivalent:

<code>
List CUSTOMERS With Any CONTACTS Containing "SMITH"
</code>

<code>
List CUSTOMERS With CONTACTS [] "SMITH"
</code>

The following example list invoices with INV_NO beginning with "C":

 
<code>
List INVOICES With INV_NO ] "C"
</code>

This next example reports the number of customers with company name ending in "INC.":

 
<code>
CountRows CUSTOMERS With COMPANY [ "INC."
</code>


These next three commands use comparison words as an actual part of the value. They are equivalent to the previous three examples:

<code>
List CUSTOMERS With CONTACTS "[SMITH]"
</code>

<code>
Sort INVOICES With INV_NO "C]"
</code>

<code>
CountRows CUSTOMERS With COMPANY "[INC."
</code>

==== Options ====

^Option^Description^
|//value//|//value// is the value that will be compared against the data in the column represented by COLUMN. The comparison value can be a literal value (text or numeric), or can be the name of another column in the row. If //value// is a literal text value, it should be enclosed in quotes.|
|Matches|If the comparison word Matches is used, With will not do a direct comparison against the comparison value. Instead, it will determine whether the data in the column represented by COLUMN follows the pattern specified in //pattern//.|
|//pattern//|When using Matches, With matches the data in the column against the mask in //pattern//. The pattern should always be in quotes. If the pattern contains literal data, the literal data should be in single quotes, and the entire pattern in double quotes.|

Some examples of patterns follow:

^Pattern^Description^
|NA|Use nA to test for n numbers of alphabetical characters.|
|NN|Use nN to test for n numbers of numerical characters.|
|NX|Use nX to test for n number of any characters.|
|NZ|Use nZ to test for up to n number of characters.|
|"string"|Use "string" to test for exact text.|
|Range|Use range to specify that the data in the column represented by COLUMN does not have to meet a specific value. Instead, the data is to fall within a specified set of values (inclusive).|


Ranges for the data values are specified by using the keywords:

From...To

- OR -

Between...And

The starting comparison value follows From or Between. The ending comparison value follows To or And. Examples of //ranges// are:

<code>
List INVOICES With INV_TOTAL From '1000' To '10000'
</code>

<code>
List INVOICES With INV_TOTAL Between '1000' And '10000'
</code>