Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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


Comments

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

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


  • Thanks again Navin.

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



  • 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
  • hello everybody 
    would you explain, how can i declare a .(dot) special character in table of sql
    for example like [email protected]  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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.