// Table variable V[shipments]
// lists materials with delayed goods shipments
if not V[shipments]
CreateTable V[shipments] material quantity delay remark
endif
Table (5,40) (12,109) _
title="Delayed Shipment of Goods" _
name="shipments" _
fixedColumns=4
Column "Material" size=12 name="material"
Column "Quantity" size=8 name="quantity"
Column "Delay (hours)" size=12 name="delay"
Column "Comment" size=34 name="remark"
CallSQL errcount =
"SELECT count(*) FROM V[shipments] WHERE remark = '' AND material <> '' "
CallSQL delayedCount =
"SELECT count(*) FROM V[shipments] WHERE CAST(delay AS INTEGER) >= 24 "
CreateTable V[shipments2] material quantity delay remark
CallSQL shipments2 =
"SELECT * FROM V[shipments] WHERE CAST(delay AS INTEGER) >= 24 "
CallSQL _
"UPDATE V[shipments] SET remark = 'Situation unclear' WHERE remark = '' "
CreateTable V[changes] material quantity delay remark
CallSQL changes =
"UPDATE V[shipments] SET remark = 'Situation unclear' " && _
"WHERE remark = '' " && _
"RETURNING *"
M1000 2 2 Situation unclear
X7292 1 4 Situation unclear
CallSQL
"UPDATE shipments SET delay = max(0, min(CAST(delay AS INTEGER), 96))"
CallSQL tempshipments = _
"SELECT material as m, quantity FROM V[shipments]" _
mode="createFromResult"
[{"m": "M1000", "quantity": "2"}, {"m": "L1286", "quantity": "10"}, {"m": "X7292", "quantity": "1"}, {"m": "M10004", "quantity": "1"}]
CallSQL materials = _
"SELECT DISTINCT material from V[shipments] ORDER BY material" _
mode="createFromResult"
L1286
M1000
M10004
X7292
CreateTable V[customers] account name city country
CreateTable V[countries] country countryname
// fill the two tables, e.g. OData or database select
// ...
// join the tables
CreateTable V[customerinfo] account name city country countryname
CallSQL customerinfo = _
"SELECT c.account, c.name, c.city, co.countryname " && _
"FROM V[customers] as c LEFT OUTER JOIN V[countries] as co " && _
"ON c.country = co.country"
CreateTable V[orders] ... equnr ...
CallSQL "CREATE INDEX idx_order_equnr ON V[orders](equnr)"
CallSQL "CREATE TABLE allshipments AS SELECT * FROM V[shipments]"
CallSQL materials = _
"SELECT DISTINCT material FROM allshipments ORDER BY material"
CallSQL "ATTACH DATABASE 'F:/countries.sqlite3' AS geo "
CreateTable V[countries] name population gdp gdprel
CallSQL countries = _
"SELECT name,population/1000 as population," && _
"gdp,gdp*1000/population as gdprel " && _
"FROM geo.countries " && _
"WHERE population>0 AND gdprel>0 " && _
"ORDER BY gdprel DESC"
Table (0,0) (12,64) name="countries" fixedColumns=3 _
title="GDP per Inhabitant, as of December 2025"
Column "Country" -readOnly size=22 name="name"
Column "GDP per capita (in $'000)" size=22 name="gdprel" -alignRight -readOnly
Column "Population in 1000" size=16 name="population" -alignRight -readOnly
CreateTable V[orders] _
SalesOrder SalesOrderType SalesOrderDate SoldToParty TotalNetAmount
CreateTable V[items] _
SalesOrder SalesOrderItem Material RequestedQuantity RequestedQuantityUnit
// Read consignment orders via SAP OData
CallVB guinet.odata.read _
service:="sap/opu/odata/sap/API_SALES_ORDER_SRV/A_SalesOrder" _
filter:="SalesOrderType eq 'CCFU' or SalesOrderType eq 'CCIS' " _
expand:="to_Item" _
orderby:="SalesOrder" _
result:="orders,items"
// Save the orders into a permanent database
// The file can be empty,
// it is automatically created as SQLite database
CallSQL "ATTACH DATABASE 'F:\consignments.db' AS snap"
CallSQL "CREATE TABLE snap.orders AS SELECT * FROM V[orders]"
CallSQL "CREATE TABLE snap.items AS SELECT * FROM V[items]"
CallSQL "ATTACH DATABASE 'F:\consignments.db' AS consi"
CallSQL orders = _
"SELECT * FROM consi.orders" _
mode="createFromResult"
CallSQL items = _
"SELECT * FROM consi.items" _
mode="createFromResult"