How to declare a variable in Vertica and then exec ?

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

Comments

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



  • 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


  • 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

Leave a Comment

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