[SQLState 22003] A character string failed conversion to a numeric value.

Connectivity
Enthusiast

[SQLState 22003] A character string failed conversion to a numeric value.

I was trying to do following with Teradata JDBC (14.00.00.09)

String sql = "SELECT DISTINCT CASE WHEN 'All' = ? THEN LOV.VAL  ELSE ? END  FROM  LOV"; 

ps = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY );

And it gives me following error:

com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata Database] [TeraJDBC 14.00.00.09] [Error 3535] [SQLState 22003] A character string failed conversion to a numeric value.

Is bind variable in CASE WHEN statmenet not supported ? or is this a bug ?

Thank you.

Hide

Tags (1)
6 REPLIES
Teradata Employee

Re: [SQLState 22003] A character string failed conversion to a numeric value.

More information would be needed in order to determine the problem.

Please post your Java code that binds values to the parameter markers, and please post the DDL for the "LOV" object.

Enthusiast

Re: [SQLState 22003] A character string failed conversion to a numeric value.

Thank you for your reply.Here is the DDL

CREATE MULTISET TABLE Test.LOV ,FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      VAL VARCHAR(60) CHARACTER SET LATIN CASESPECIFIC NOT NULL,

      ROW_ID INTEGER NOT NULL)

PRIMARY INDEX ( VAL );

And here is the java code snippet:

Connection conn = .. snip.. // get a connection first..

PreparedStatement  ps = null;

try

{

  String sql = "SELECT DISTINCT CASE WHEN 'All' = ? THEN LOV.VAL  ELSE ? END  FROM  LOV";

   // get prepared statement

   ps = conn.prepareStatement(sql,     ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY );

   // NOTE: Error raised here even before I bind parameters

   // bind parameters

   long currTime = (new java.util.Date()).getTime();

   ParameterMetaData pmd = ps.getParameterMetaData();

         int pcount = pmd.getParameterCount();

         for(int i=1; i<=pcount;i++)

         {

           int paramType = pmd.getParameterType(i);

           switch(paramType)

           {

             case java.sql.Types.NUMERIC: case java.sql.Types.INTEGER: case java.sql.Types.FLOAT:

             case java.sql.Types.DECIMAL:  case java.sql.Types.BIGINT:

             case java.sql.Types.SMALLINT:case java.sql.Types.TINYINT:

                ps.setInt(i, 0);

                break;

             case java.sql.Types.DOUBLE:   

                ps.setDouble(i, 0);

                break;

             case java.sql.Types.VARCHAR: case java.sql.Types.CHAR: case java.sql.Types.NVARCHAR:

             case java.sql.Types.NCHAR: 

                ps.setString(i, "0");

                break;

             case java.sql.Types.DATE:

                ps.setDate(i, new java.sql.Date(currTime));

                break;

             case java.sql.Types.TIME:

                ps.setTime(i, new java.sql.Time(currTime));

                break;

             case java.sql.Types.TIMESTAMP:

                ps.setTimestamp(i, new java.sql.Timestamp(currTime));

                break;

             default:

                ps.setNull(i, paramType);

           }

         }

         // exeucte the query;

         ResultSet rs = ps.executeQuery();

     }

     catch(Exception ex)

     {

        System.out.println(ex);

     }


Teradata Employee

Re: [SQLState 22003] A character string failed conversion to a numeric value.

You have encountered a limitation with the Teradata Database.

For the question-mark parameter marker in the expression "CASE WHEN 'All' = ?", the Teradata Database is making the wrong assumption about the data type of the value that your application will later bind to the parameter marker. Instead of assuming that your application will bind a character value, the Teradata Database is assuming that your application will bind a numeric value.

The workaround is to add a cast around the parameter marker:

String sql = "SELECT DISTINCT CASE WHEN 'All' = cast(? as varchar(60)) THEN LOV.VAL ELSE ? END FROM LOV";

Enthusiast

Re: [SQLState 22003] A character string failed conversion to a numeric value.

Thank you !

I just changed my query as suggested and now following line works fine.

 ps = conn.prepareStatement(sql,     ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY );

However,  ParameterMetaData.getParameterCount() returns 4 where it should be two..

Any thought ?

 ParameterMetaData pmd = ps.getParameterMetaData();

 int pcount = pmd.getParameterCount();  // this gives 4 instead of 2..

Teradata Employee

Re: [SQLState 22003] A character string failed conversion to a numeric value.

You found a bug in the Teradata Database.

Here are my testing results:

TD 12.00.03.28 - correct - getParameterCount=2

TD 13.00.01.13 - incorrect - getParameterCount=4

TD 13.10.01.05 - incorrect - getParameterCount=4

TD 14.0 - correct - getParameterCount=2

If you're a customer, then I recommend that you open an incident with Teradata Customer Support, so you can get a fix for the Teradata Database release that you're using.

Enthusiast

Re: [SQLState 22003] A character string failed conversion to a numeric value.

Great, thank you for your investigation.

I will ask the end cutomer to open a service request for this issue.