How to declare a variable in SQL Script?

Neil_1

In other database platforms it is possible to declare a variable that will hold a single value. Is this possible in Vertica, googling and searching the docs I haven't discovered that it is.

Eg, in Transact SQL:
DECLARE @lastIdentityValue smallint;
SELECT @lastIdentityValue = SCOPE_IDENTITY();

Navin_C

Hi Neil,

You can set a variable in this way

\set variable_name value

example :
\set lastIdentityValue "value"

You can check your variable like this:

select :lastIdentityValue ;

Also one more way to check:

\echo :lastIdentityValue

In Vertica usage of variable is not very flexible. So you need to use some scripting language to use variables with vertica.

Hope this helps

Neil_1

Thanks Navin.

I had tried using this, but found an issue:

dbadmin=> \set id = LAST_INSERT_ID();

dbadmin=> SELECT :id;ERROR 4856:  Syntax error at or near "=" at character 8
LINE 1: SELECT =LAST_INSERT_ID();

Is there a way to have the subject of the assignment evaluated? (eg: so that I get the value of the LAST_INSERT_ID() rather than the text?

Thanks,

Neil

Navin_C

Hi Neil

You tried 
dbadmin=> \set id = LAST_INSERT_ID();
Instead Try this

dbadmin=> \set id LAST_INSERT_ID

Currently I dont't think there is way to assign a function to a variable,
As I said Vertica variables are not very flexible.

You can get some help with this:

\set t_pwd 'pwd'
\set file_path :t_pwd/dir_name

This is how I use one variable into another variable.

Hope this helps

[Deleted User]

Hi all,

Thanks Navin for answering this; you're right (and Neil is on the right track), that's really all we provide by way of variables at this point.

That said, if I can just jump in briefly:  In some cases, people find our WITH statement to be useful:

https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/SELECT/WITH...

For recordsets, "CREATE LOCAL TEMP TABLE <name> AS SELECT ..." or "CREATE LOCAL TEMP VIEW <name> AS SELECT ..." (depending on whether you want eager or lazy evaluation, respectively) is functionally equivalent to a variable containing a recordset, though it is certainly a more-cumbersome syntax.

Adam

Neil_1

Thanks for this Adam, in particular the lazy evaluation version of a Common Table Expression which I wasn't aware of.

The concrete issue that I'm trying to solve using a variable is the assignment of the LAST_INSERT_ID() value to a variable, such that it can be used in subsequent statements (both as a value in insert statements, and as an argument supplied to where clause predicates in select statements.

Issues I've encountered include:

• Meta-function last_insert_id cannot be used in INSERT
• Meta-function ("last_insert_id") cannot be used with non-Select clauses

We're considering moving to using sequences rather than autoincrement constraints as a consequence of this.

Any advice on this?
Does using a sequence rather than an autoincrement column constraint bring any performance penalties?

Thanks,

Neil

Neil_1

Thanks again Navin.

Not trying to use a non-existent = assignment operator is the trick! Thanks!

Neil_1

Slight update to this...

Following from Navin's advice above I'm now using a variable with the auto_increment.

Would still be interested in any details on the tradeoff between sequences, auto_increment, and identity in terms of performance.

Thanks,

Neil

Raviteja

hello everybody 
would you explain, how can i declare a .(dot) special character in table of sql
for example like exam.rk@gmail.com  then here not allowed a . character. so kindly give responce to me
Thank you!

YO_rch

Hi, I used this,  a table instead a variable

 

 

DROP TABLE IF EXISTS MySCHEMA.Temp_Calendar;

 

CREATE TEMP TABLE MySCHEMA.Temp_Calendar
ON COMMIT PRESERVE ROWS
AS(
        Select     Current_Date as Fecha
);

Select Fecha From VariableCompensation.Temp_Calendar

 

you can use the field as a result of subquery

 

 

 

 

SowmyaB

is there a way to pass variables while running the queries via jdbc ? If so , could some one send some samples how to achieve that? i am basically looking for equivalent of  vsql -v option in jdbc api's.