HP Vertica Sample SQL

gistfile1.txt

```sql
-- hp vertica sample sql

CREATE VIEW myview AS
SELECT SUM(annual_income), customer_state
FROM public.customer_dimension
WHERE customer_key IN
(SELECT customer_key
FROM store.store_sales_fact)
GROUP BY customer_state
ORDER BY customer_state ASC;

INSERT INTO t1 (col1, col2) (SELECT 'abc', mycolumn FROM mytable);

MERGE INTO t USING s ON (t.c1 = s.c1)
WHEN NOT MATCHED THEN INSERT (c1, c2) VALUES (s.c1, s.c2);

-- First WITH clause,regional_sales
WITH
regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region),
-- Second WITH clause top_regions
top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > (SELECT SUM (total_sales)/10 FROM regional_sales) )
-- End defining WITH clause statement
-- Begin main primary query
SELECT region,
product,
SUM(quantity) AS product_units,
SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
;
```