Informix Sample SQL

gistfile1.txt

```sql
-- Informix sample sql
CREATE VIEW myview (cola, colb) AS
SELECT colx, coly from firsttab
UNION
SELECT colx, colz from secondtab;

CREATE VIEW palo_alto AS
SELECT * FROM customer WHERE city = 'Palo Alto'
WITH CHECK OPTION
;

MERGE INTO t2 AS o USING t1 AS n ON o.f1 = n.f1
WHEN NOT MATCHED THEN INSERT ( o.f1,o.f2)
VALUES ( n.f1,n.f2)
;

INSERT INTO t2(f1, f2)
SELECT t1.f1, t1.f2 FROM t1
WHERE NOT EXISTS
(SELECT f1, f2 FROM t2
WHERE t2.f1 = t1.f1);

MERGE INTO sale USING new_sale AS n
ON sale.cust_id = n.cust_id
WHEN MATCHED THEN UPDATE
SET sale.salecount = sale.salecount + n.salecount
WHEN NOT MATCHED THEN INSERT (cust_id, salecount)
VALUES (n.cust_id, n.salecount);


MERGE INTO customer c
USING ext_customer e
ON c.customer_num=e.customer_num AND c.fname=e.fname AND c.lname=e.lname
WHEN NOT MATCHED THEN
INSERT
(c.fname, c.lname, c.company, c.address1, c.address2,
c.city, c.state, c.zipcode, c.phone)
VALUES
(e.fname, e.lname, e.company, e.address1, e.address2,
e.city, e.state, e.zipcode, e.phone)
WHEN MATCHED THEN UPDATE
SET c.fname = e.fname,
c.lname = e.lname,
c.company = e.company,
c.address1 = e.address1,
c.address2 = e.address2,
c.city = e.city,
c.state = e.state,
c.zipcode = e.zipcode,
c.phone = e.phone ;


UPDATE nmosdb@wnmserver1:test
SET name=(SELECT name FROM test
WHERE test.id = nmosdb@wnmserver1:test.id)
WHERE EXISTS(
SELECT 1 FROM test WHERE test.id = nmosdb@wnmserver1:test.id
);

UPDATE orders
SET ship_charge =
(SELECT SUM(total_price) * .07 FROM items
WHERE orders.order_num = items.order_num)
WHERE orders.order_num = 1001;
```