How to declare a variable in SQL Script?
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();
Eg, in Transact SQL:
DECLARE @lastIdentityValue smallint;
SELECT @lastIdentityValue = SCOPE_IDENTITY();
0
Comments
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
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
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
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
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
Not trying to use a non-existent = assignment operator is the trick! Thanks!
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
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!
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
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.