Hi,
I have defined a vsql variable:
\set MPcode 'LIPI3';
now I would like to use it like so:
select MasterProductID from sandbox.DimProductMaster where MasterProductCode = :MPcode;
where MasterProductCode is a varchar but I get the error
ERROR 2624: Column "LIPI3" does not exist
How do I specify the variable to get this match to work?
CAn you use a vsql variable in a where statement which expects a varchar
Moderator: NorbertKrupa
Re: CAn you use a vsql variable in a where statement which expects a varchar
Well a little more playing around and I got this to work:
sandbox=> \set MPcode to_char('LIPI3');
sandbox=> \echo :MPcode
to_char('LIPI3');
sandbox=> select MasterProductID from sandbox.DimProductMaster where MasterProductCode = :MPcode;
MasterProductID
-----------------
4867
(1 row)
sandbox=> \set MPcode to_char('LIPI3');
sandbox=> \echo :MPcode
to_char('LIPI3');
sandbox=> select MasterProductID from sandbox.DimProductMaster where MasterProductCode = :MPcode;
MasterProductID
-----------------
4867
(1 row)
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: CAn you use a vsql variable in a where statement which expects a varchar
You can also do it like this:
Code: Select all
dbadmin=> select * from t1;
c1 | c2
----+------
1 | Jim
2 | Jane
(2 rows)
dbadmin=> \set v '''Jane'''
dbadmin=> select * from t1 where c2 = :v;
c1 | c2
----+------
2 | Jane
(1 row)
Last edited by NorbertKrupa on Wed Mar 18, 2015 9:48 pm, edited 1 time in total.
Reason: fixed typo (missing the word "like")
Reason: fixed typo (missing the word "like")
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.