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)
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