I am Creating SSRS reports using Vertica Data Source. I need to insert values to vertica physical table using SSRS input parameters. Build query like this:INSERT INTO Employee VALUES (@GUID, @EmpNo, @EmpName);
While executing it thorows error:
ERROR: Syntax error at or near ","
[42601] ERROR: Syntax error at or near ","
Meanwhile, select query works fine with SSRS input parameters, query like this: select * from Employee where GUID = @GUID.
Any help on this would be greatly appreciated.
Vertica Input Query does not support SSRS Input Parameters
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Vertica Input Query does not support SSRS Input Paramete
Are you sure the "select * from Employee where GUID = @GUID" SQL is working in Vertica as you'd expect?
The @ symbol is used for "Absolute Value" in Vertica.
Example:
If this is not the issue, if you check the Vertica log, does your statement "INSERT INTO Employee VALUES (@GUID, @EmpNo, @EmpName);" show up with actual values substituted in the variables? If so, are there single quotes surrounding the VARCHAR values? Is there a comma in the values?
The @ symbol is used for "Absolute Value" in Vertica.
Example:
Code: Select all
dbadmin=> create table jim (num int);
CREATE TABLE
dbadmin=> insert into jim values (5);
OUTPUT
--------
1
(1 row)
dbadmin=> insert into jim values (-5);
OUTPUT
--------
1
(1 row)
dbadmin=> select num, @num abs_val from jim;
num | abs_val
-----+---------
5 | 5
-5 | 5
(2 rows)
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.
Re: Vertica Input Query does not support SSRS Input Paramete
Thanks for reply knicely87. "select * from Employee where GUID = @GUID" it works as i exect. I have created many reports like this. No comma in the values for insert. Even it does not work on integer columns. Please suggest any workaround how to insert table from SSRS.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Vertica Input Query does not support SSRS Input Paramete
Hmm. Have you tried re-writing the INSERT? Like this?
INSERT INTO Employee SELECT @GUID, @EmpNo, @EmpName;
INSERT INTO Employee SELECT @GUID, @EmpNo, @EmpName;
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.
Re: Vertica Input Query does not support SSRS Input Paramete
Have tried. Throws below error,
Unable to match all parameters in the collection with the command.
Input parameter named "GUID" was not found or was incompatible.
Unable to match all parameters in the collection with the command.
Input parameter named "GUID" was not found or was incompatible.