How to declare a variable in Vertica and then exec ?

Lan_1

I am a new guy to  Vertica，
now I need to migrate a sql from sqlserver to Vertica,

Declare @SQL varchar(max)SET @SQL ='select c.pid,''Not Exists'' as Pstate from( SELECT '''+REPLACE(@JoinID,',',''' pid UNION ALL SELECT ''')+''' pid ) c where c.pid not in
(SELECT   pn_id FROM  pytable )' 
exec(@SQL)
else
begin
select null as Psatet, null as split 
end

How to declare a variable in Vertica and then exec ? 
Thank you

Navin_C

Hi Lan,

Vertica does not have an extensive support for variables.

They are very much limited when it comes to use them in SQL.

For declaring a variable simply do this.

\set variable_name 'variable_value'

For your scenario, you can put to use this example

Example :

nnani=> \set SQL 'select * from navin.test1'  nnani=> :SQL;
 id | name
----+------
  3 | cbc
  2 | sbc
  1 | abc
  4 | dbc
  5 | ebc
(5 rows)

Here SQL is the variable name and then in inverted commas is the SQL value you want to execute.
Then you call your variable simple using the colon operator

You can also include/call a variable into another variable like your SQL above.
However this is not very handy and not used too much in production, but you can do it

nnani=> \set TABLENAME 'navin.test1'
nnani=> \set SQL 'select * from ':TABLENAME'';
Time: First fetch (0 rows): 1.783 ms. All rows formatted: 1.783 ms
dbadmin=> :SQL;
 id | name
----+------
  2 | sbc
  3 | cbc
  1 | abc
  4 | dbc
  5 | ebc
(5 rows)

This way you can use variables in Vertica.

Some notes on variables:

1. They don't come with any data type like in SQL server
2. They are session specific

Hope this helps.

Lan_1

Thanks for your reply Navin,

I need to use the sql in a report that the data source pointed to a vertica

when I set the commandTest like this:
        set v1 'NTR.pytable'; select pn_id from :v1

It shows error:

Unsupported SET option v1
knows what to do ? thanks

Navin_C

Hi Lan,

For now the set command works only from vsql utility.


Workaround in your case:

Try going for External Procedures, and then you can call the external procedure from your report.

Hope this helps

Stelios_Hadjist

Hi Lan, This will not work: set v1 'NTR.pytable'; select pn_id from :v1 Put the commands on a script and then call the script (\set has to be on it's own line): vi t1 \ set v1 'NTR.pytable' select pn_id from :v1; vsql -U dbadmin -f t1