Purpose Use the built-in functions min(), max(), count()
and sum() of the
SQL database to determine
the minimun, maximum, count and sum() of a group of table rows.
Please note: If you need more than one of these functions in
a database call, you need to formulate it as a "join" of two tables,
since /guixt/dbselect does not allow us to specify different target
fields for simple select calls (one table).
We use the function module /guixt/dbselect and the Open Call
Interface.
Example 1 We display a table which shows the highest
document
number for each sales document type:
// GuiXT table
CreateTable V[vbakstat] auart text min max count
// SQL parameters
set V[tablename] "VBAK JOIN TVAKT ON VBAK~AUART = TVAKT~AUART"
set V[fields] "VBAK~AUART,TVAKT~BEZEI,MIN( VBAK~VBELN ),
MAX( VBAK~VBELN ),COUNT(*) AS I1"
set V[condition] "TVAKT~SPRAS = '&V[_language]'"
set V[orderby] "VBAK~AUART"
set V[groupby] "VBAK~AUART,TVAKT~BEZEI"
// database call
Call "/GUIXT/DBSELECT" _
export.table="tablename" _
export.fields="fields" _
export.condition="condition" _
export.orderBy="orderby" _
export.groupby="groupby" _
import.values="vbakstat"
Remark If you need several functions min(), max(), count()
in one statement without needing a join, you may force the "join" syntax
of /guixt/dbselect with a "dummy" join of the same table. Example:
GuiXT
// GuiXT table
CreateTable V[vbakstat] auart min max count
// SQL parameters
set V[tablename] "VBAK JOIN VBAK AS VBAK2 ON VBAK~VBELN = VBAK2~VBELN"
set V[fields] "VBAK~AUART,MIN( VBAK~VBELN ),MAX( VBAK~VBELN ),COUNT(*) AS I1"
set V[orderby] "VBAK~AUART"
set V[groupby] "VBAK~AUART"
// database call
Call "/GUIXT/DBSELECT" _
export.table="tablename" _
export.fields="fields" _
export.condition="condition" _
export.orderBy="orderby" _
export.groupby="groupby" _
import.values="vbakstat"