Create a variable in Vertica

Question

I am transitioning from SQL Server to Vertica. Is there any comparable way to create a variable?

Usually I do something like:

Define @myVariable int
Set @myVariable = select MAX(Field1) from myTable

Answer 1

I do not think Vertica allows variables, except if you are using vsql directly, but then vsql variables are very limited and will not do what you expect:

-- this will work
\set a foo
\echo :a
foo

-- this is not what you expect:
\set a NOW()
\echo :a
NOW()

\set a select max(id) from s.items()
\echo :a
selectmax(id)froms.items()

See for more information the vertica doc at https://my.vertica.com/docs/6.1.x/HTML/index.htm#2732.htm

Answer 2

You do not "create variables" in Vertica the same way you do not "create variables" in SQL Server. What you're trying to convert is a T-SQL script.

You can do the same in Vertica by creating Perl or Python or Java ... scripts running outside the database or writing a user defined function in C++ or R or Java running inside Vertica.