Syntax Diagram

(f|v)Subject [Alias-List|Aggregation] (Condition)

Subject

A request must have a Subject, which is a table or view name starting with either f or v.

Examples

Address Book Table.

f0101

Open Purchase Order by Item.

v4311jo

Submitting a Subject only request will fetch all fields of up to Maximum Rows.

Alias-List

An Alias-List is a comma separated list of table’s or view’s aliases to list. The table prefix can be ignored if the alias is unique, like in tables for example.

A query can have either Alias-List or Aggregation.

Examples

List Address Book’s name and number.

f0101 (an8,alph)

List branch, document number and type and open quantity on F4311 and F4316.

v4311jo (mcu,f4311.doco,f4311.dcto,f4311.uopn,f4316.uopn)

Note: The mcu alias does not need to be prefixed with table since it’s only in F4311.

Aggregation

The following aggregate functions are available:

• sum
• min
• max
• avg
• count
• count_distinct
• avg_distinct
• group

In addition there are two sequence functions:

• desc
• asc

The form of an aggregate is:

Aggreate-Function(Alias-List)

Note: Items in the Alias-List must be prefixed with the table.

Examples

Show highest, lowest, average and total order line amounts.

f4311 [max(f4311.aexp) min(f4311.aexp) avg(f4311.aexp) sum(f4311.aexp)]

Repeat the above, but group and sort the result by supplier and count orders and lines.

f4311 [
	max(f4311.aexp)
	min(f4311.aexp)
	avg(f4311.aexp)
	sum(f4311.aexp)
	count_distinct(f4311.doco,F4311.dcto)
	count(f4311.an8)
	group(f4311.an8)
	asc(f4311.an8)]

Note: The count_distinct function counts rows with unique doco and dcto.

Condition

The form of a condition is:

(all|any)(Alias Operator Literal(s,)... ...)

The all prefix requires that all condidtions must be met and any requires that one of the condition must met (AND/OR equivalent).

The available Operators are:

• Equal =
• Greater Than >
• Less Than <
• Greater or Equal >=
• Less or Equal <=
• Not Equal <>
• Between bw
• In List in
• String Contains ?
• String is Blank _
• String is Not Blank !
• String Starts With ^
• String Ends With $

Examples

List Sum of Open OP Orders.

f4311 [
	group(f4311.doco,f4311.dcto) sum(f4311.aexp)]
	all(f4311.nxtr = 400 f4311.dcto = OP f4311.uopn <> 0)

List items with non-blank cat code 2 and contains “Bike” in the description.

f4101 (itm,litm,dsc1) all(f4101.srp2 ! blank f4101.srtx ? Bike)

Note: The ! operator only require a literal to keep up with the syntax.

List Work Orders with Status between 10 and 40.

f4801 (doco,dl01,srst) all(f4801.srst bw "10","45")

Note: Literals can optionally be enclosed in quotation marks which is useful when it contains special characters.