Skip to main content

Smart Parms Criteria Entry Rules

When using Standard Parameters, the user must know various SQL rules for working with text fields, numeric values, lists, and ranges of values. Smart Parms simplify query writing by using standard rules for processing parameters. The rules used by Smart Parms are similar to those used in Spreadsheet Server, for specifying segment values.

See the following table for a comparison, when specifying criteria values used by Standard Parameters, versus Smart Parms:

Note: The following table uses SQL (iSeries DB2) rules for the comparison.

Criteria Type

Standard Parameters

Smart Parms

Single Value: Text

='ABC'

ABC

Single Value: Numeric

=123

123

Single Value: Date

=DATE('01/01/2007')

1/1/2007

Mask (Wildcard)

LIKE 'A%'

A*

Mask (Wildcard): All Values

LIKE '%'

* or blank space

Mask (Wildcard): Single Value

LIKE '_500'

*500

Range: Text

BETWEEN 'ABC' AND 'DEF'

ABC.DEF

Range: Numeric

BETWEEN 123 AND 456

123.456

Range: Date

BETWEEN DATE('01/01/2007') AND DATE('03/31/2007')

1/1/2007.3/31/2007

Value List: Text

IN('A','B','C')

[A,B,C]

Value List: Numeric

IN(1,2,3)

[1,2,3]

Value List: With Exclude

N/A

[AAA.CCC,/BBB]

Note: May not exclude a segment list.

Field is Blank

N/A

BLANK

Field is Not Blank

N/A

NOTBLANK

Greater Than, or Equal To

>=1000

1000.99999999

Less Than, or Equal To

<=1000

-99999999.1000

Logical NOT (using alphabet as dataset)

NOT IN('A','B','C')

[D.Z]

Segment List

N/A

^Country

@ Field

N/A

One of the following:

  • @alias:value
  • @fieldname:value
  • @table.fieldname:value

For example, @Company:001.

Note: Alias is as defined in Application Configurator.

Note:
  • The range delimiter, list separator, and exclusion characters are defined in the user's Spreadsheet Server Settings.
  • @ Fields allow for using different fields in the file as criteria, for retrieving data without having to switch the query.
  • For Smart Parms, comparison operators (=, <>, <, >, BETWEEN, LIKE, NOT, etc.) are not used .

  • If you are using SmartParms, editing the criteria value in the criteria builder results in an error.

Was this article helpful?

We're sorry to hear that.

Powered by Zendesk