Vertica Input Query does not support SSRS Input Parameters

Moderator: NorbertKrupa

Post Reply
San
Newbie
Newbie
Posts: 3
Joined: Fri Nov 21, 2014 8:07 am

Vertica Input Query does not support SSRS Input Parameters

Post by San » Tue Nov 25, 2014 12:15 pm

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.

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

Re: Vertica Input Query does not support SSRS Input Paramete

Post by JimKnicely » Tue Nov 25, 2014 1:36 pm

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:

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)
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?
Jim Knicely

Image

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

San
Newbie
Newbie
Posts: 3
Joined: Fri Nov 21, 2014 8:07 am

Re: Vertica Input Query does not support SSRS Input Paramete

Post by San » Tue Nov 25, 2014 2:39 pm

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.

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

Re: Vertica Input Query does not support SSRS Input Paramete

Post by JimKnicely » Tue Nov 25, 2014 2:43 pm

Hmm. Have you tried re-writing the INSERT? Like this?

INSERT INTO Employee SELECT @GUID, @EmpNo, @EmpName;
Jim Knicely

Image

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

San
Newbie
Newbie
Posts: 3
Joined: Fri Nov 21, 2014 8:07 am

Re: Vertica Input Query does not support SSRS Input Paramete

Post by San » Wed Nov 26, 2014 7:14 am

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.

Post Reply

Return to “New to Vertica”