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 orders, lineitems, parts
   WHERE orders.orderid = lineitems.orderid
     AND lineitems.itemno = parts.partno;

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