Comments

• Navin_C Vertica Customer ✭

  March 2014

  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

  0
• Neil_1

  March 2014

  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
  
  

  0
• Navin_C Vertica Customer ✭

  March 2014

  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

  0
• [Deleted User]

  March 2014

  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

  0
• Neil_1

  March 2014

  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
  
  
  

  0
• Neil_1

  March 2014

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

  0
• Neil_1

  March 2014

  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

  0
• Raviteja

  October 2014

  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!
  
  

  0
• YO_rch

  June 2015

  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

   

   

   

   

  0
• SowmyaB

  March 2016

  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.

  0