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
   WHERE orders.orderid = lineitems.orderid;

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