OPEN DATABASE "db";

SET SYNTAX = "1";

FIELD lineitems.delivdate DISPLAY AS DATE("%d%m%y");

FIELD icnt = IF (itemcount = "1", 10,
                    IF (itemcount = "2", 100,
                       IF (itemcount = "3", 1000, 1)))
                  DISPLAY AS INT(4);

FIELD amount = (qty * price / icnt)
                 DISPLAY AS MONEY(10, 2);

CREATE VIEW temp AS SELECT *
   FROM customers, orders, lineitems, parts
   WHERE customers.custno = orders.custno
     AND orders.orderid = lineitems.orderid
     AND lineitems.itemno = parts.partno;

SELECT
   custno, matchcode, name1, name2,
   delivdate, orderno, ordertype, orderstat,
   itemno, descripa, descripb,
   qty, price, icnt, amount
FROM temp
WHERE ordertype = "VK";
