Oracle's 'GENERATED ALWAYS' syntax equivalent in Vertica?

Question

I apologize for not having much background on this question but I've been tasked with switching some of our Oracle queries to Vertica syntax and I'm having trouble understanding the documentation around the GENERATED ALWAYS Oracle command as it relates to case statements.

From what I've found, it seems like the GENERATED ALWAYS in Oracle is equal to AUTO INCREMENT in Vertica.

Here is an example of a case statement that I need to rewrite in Vertica. At first glance, it looks like it's just telling it to use an alias, but I'm not sure I'm understanding that properly.

FIELD_NAME varchar2(25) GENERATED ALWAYS as(
      case "FIELD_NAME"
        when 'ABC'
        then 'ABC / Category_ABC'
        when 'DEF'
        then 'DEF / Category_DEF'
        else 'Other'
      end)

Would this essentially be the same? Is it safe to simply just remove the GENERATED ALWAYS piece? Or is something bigger happening here?

FIELD_NAME varchar2(25) as(
      case "FIELD_NAME"
        when 'ABC'
        then 'ABC / Category_ABC'
        when 'DEF'
        then 'DEF / Category_DEF'
        else 'Other'
      end)

The terminology that you're looking for (at least, in Oracle) is Virtual columns. Oracle supports them from (IIRC) 11g. Vertica may or may not ([as of 2012 it didn't](http://vertica-forums.com/viewtopic.php?t=430), but 7 years is a long time!), but if it doesn't, you could always have a view on the table which contains the generated column. — Boneist, Jan 10 '20 at 16:31

score 1 · Accepted Answer · answered Jan 13 '20 at 23:40

This is by no means a query, context, phenderbender. It's a data definition context. GENERATED ALWAYS or GENERATED BY DEFAULT is a column attribute that you define when you either create a table, or alter a table to create or modify a column.

Vertica's syntax for that is the DEFAULT clause of a column definition.

If I look at the Oracle docu, here:

https://oracle-base.com/articles/11g/virtual-columns-11gr1

I would write their example in Vertica like this:

CREATE TABLE employees (
  id          INTEGER,
  first_name  VARCHAR(10),
  last_name   VARCHAR(10),
  salary      NUMERIC(9,2),
  comm1       NUMERIC(3),
  comm2       NUMERIC(3),
  salary1     NUMERIC(9,2) DEFAULT (ROUND(salary*(1+comm1/100),2)),
  salary2     NUMERIC(9,2) DEFAULT (ROUND(salary*(1+comm2/100),2)),
  CONSTRAINT employees_pk PRIMARY KEY (id)
);

INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (1, 'JOHN', 'DOE', 100, 5, 10);

INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (2, 'JAYNE', 'DOE', 200, 10, 20);
COMMIT;

SELECT * FROM employees;
-- out  id | first_name | last_name | salary | comm1 | comm2 | salary1 | salary2 
-- out ----+------------+-----------+--------+-------+-------+---------+---------
-- out   1 | JOHN       | DOE       | 100.00 |     5 |    10 |  105.00 |  110.00
-- out   2 | JAYNE      | DOE       | 200.00 |    10 |    20 |  220.00 |  240.00
-- out (2 rows)
-- out 
-- out Time: First fetch (2 rows): 182.567 ms. All rows formatted: 182.674 ms

Oracle's 'GENERATED ALWAYS' syntax equivalent in Vertica?

1 Answers1