Hello, I have installed TD VS 15.11, I connect to TD v. 15.10.1.04, with TD JDBC connection.
When I try to use a parameter for user input in a query script, I get error:
Executed as Single statement. Failed [3535 : 22003] A character string failed conversion to a numeric value.
And the SQL code syntax of using the parameter is, or as many varitions I've tried:
WHERE active_date BETWEEN ?\date1 and ?\date2
WHERE active_date BETWEEN ?date1 and ?date2
WHERE active_date BETWEEN '?date1' and '?date2'
WHERE active_date BETWEEN '?\date' and '?\date2'
And nothing seems to pass. What is the proper syntax or usage of Parameters using the TD VS tool?
I assume by "Teradata VS" you mean "Teradata Studio".
I don't think the problem is with the parameter - the \?date1 is the correct format for the SQL editor to bring up the Configure Parameters dialog.
I suspect the problem is the format of the input. Since this isn't a stored procedure call, etc. the editor won't know the data type for the parameter, so it defaults to a character literal. By default, Studio uses ANSI semantics, so the implicit conversion to DATE will expect yyyy-mm-dd format; otherwise use explicit CAST(\?date1 AS DATE FORMAT'...').
Sorry, yes, Teradata Studio. I tried \?date1 and I get error reason Failed to execute SQL: Parse Error splitting statement.
I used to use SQL Assitant and prompt the user for a date with '?date1' and it was ok, now nothing seems to work in replicating the same promot for the user in Teradata Studio. I've hunted around with no sucess in some setting for parameters in the tool, perhaps there is a setting I need to change?
My typo. For TD Studio, the syntax should be ?\date1 as in your example.
Note that SQL Assistant "parameter substitution" replaces the marker with the entered text, before sending the modified SQL statement to the database. So you can enter date values as '2015-07-30' or date'2015-07-30' or even CAST('07/30/2015' as DATE FORMAT 'mm/dd/yyyy')
But TD Studio prepares a parameterized query and then binds the text as a VARCHAR value. So you would enter only 2015-07-30 for the date.
In using TD Studio, I'm not getting to the point of getting prompted to input any data from the user when running a script, I get an error about character conversion. I have a script, and it has two dates, begin date and end date, that need to be user captured data inputs so then the query can then run with a between date range. I can't get the prompt up for input, only an errors, no matter what syntax format I code into the query.
Did this: WHERE aactivity_date BETWEEN ?\begin_date and ?\end_date
I get error 3706:42000 was expecting '?' at end
Then this: WHERE aactivity_date BETWEEN '?\begin_date' and '?\end_date'
I get error 3535:22003 error on character string conversion to numeric
That makes me think that the "script" execution is using the generic "SQL File Editor" instead of the "Teradata SQL Editor".
Try opening a Teradata SQL Editor window and pasting in the statement.
Your're right Fred, I'm in the SQL File Editor, how do I switch to the Teradata SQL Editor if I'm already have the Query in the SQL file editor? I can't seem to find a way to make the switch to a different editor.
Right click the file in the Project Explorer tree and select Open With / Teradata SQL Editor.
When you "run" the file again, it should remember the editor last used.
DataGeek, you've pointed out a problem we have with running parameterized SQL using the "Execute SQL Files" menu item.
As Fred has noted, if you open the file with the "Open With>Teradata SQL Editor" and run the SQL from the editor, things work.
I created a table with a column whose data type is DATE and ran the following:
select * from charles.WithDate where c2 between ?\startdate and ?\enddate;
The statement is prepared and we're told the two parameters have the data type DATE. The Configure Parameters dialog let me enter dates formatted as either YYYY-MM-DD or YYYY/MM/DD. The statement ran and returned the appropriate rows.
The error I get when trying to run with Execute SQL Files is a result of our connection pooling methods. There are two calls to the database. One to determine what the parameter types are and one to actually run the SQL. When running a file, these are done with two separate connections so the prepared statement created by the first connection being run by the second connection fails. I didn't see your problem of string conversion. Is the active_date column a DATE?