Declaring Variables in Vertica

Moderator: NorbertKrupa

Post Reply
Vertica_grm
Newbie
Newbie
Posts: 17
Joined: Tue Apr 21, 2015 8:35 pm

Declaring Variables in Vertica

Post by Vertica_grm » Sat Jul 04, 2015 1:43 pm

Hi,

I would like to understand the way I can declare and set variables as part of my SQL Query.


DECLARE @site1 INT;
/set site1 = (Query which results a count)

I'm getting Error messages "Syntax error at or near "@""
"Syntax error at or near "/""

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Declaring Variables in Vertica

Post by JimKnicely » Sat Jul 04, 2015 3:37 pm

Hi,

The \set meta-command in vsql is used to set variables. Maybe the following example can help move you in the right direction.

Code: Select all

dbadmin=> create table test (col1 int);
CREATE TABLE

dbadmin=> insert into test values (1);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test values (2);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test values (3);
 OUTPUT
--------
      1
(1 row)

dbadmin=> \set test_cnt 'select count(*) from test'
dbadmin=> :test_cnt;
 count
-------
     3
(1 row)
You can align the output and return only the the tuples...

Code: Select all

dbadmin=> \a
Output format is unaligned.

dbadmin=> \t
Showing only tuples.

dbadmin=> :test_cnt;
3
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Vertica_grm
Newbie
Newbie
Posts: 17
Joined: Tue Apr 21, 2015 8:35 pm

Re: Declaring Variables in Vertica

Post by Vertica_grm » Wed Jul 22, 2015 10:04 pm

Thanks JIM,

So i use a SQL client. DB visualizer to run all my queries and when I use /set there, it pops up a window asking for the input. It's very annoying not to be able to use queries with variables on that tool . :(

Post Reply

Return to “New to Vertica SQL”