(JTDS) java.sql.SQLException: Error converting data type nvarchar to numeric

Question

I'm having trouble reading from a view in a SQL Server 2005 database using JTDS and having a serious problem finding a solution.

The query is just selecting everything in the view:

SELECT * FROM encounters_view WHERE patient_id = ?

The exception is being thrown on this line:

rset = selectAllEncountersByPatientId.executeQuery();

The stacktrace is as follows:

java.sql.SQLException: Error converting data type nvarchar to numeric.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
    at net.sourceforge.jtds.jdbc.TdsCore.isDataInResultSet(TdsCore.java:796)
    at net.sourceforge.jtds.jdbc.JtdsResultSet.<init>(JtdsResultSet.java:134)
    at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:483)
    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:776)
    at package_name.ClassName.method(Db_query_class.java:91)
    at package_name.ClassName.main(Main_class.java:36)

View Data Types:

id (unique(numeric(16,0)),not null)
provId (uniqueid(numeric(16,0)),not null)
patId (uniqueid(numeric(16,0)),not null)
mrn (varchar(20),not null)
visitId (uniqueid(numeric(16,0)),not null)
dttm (datetime, null)
apptType (name(varchar(255)),null)
apptStatus (name(varchar(255)),null)
refProvId (uniqueid(numeric(16,0)),not null)
pay (name(varchar(255)),not null)
zip (char(10),not null)

Any help would be greatly appreciated.

Answer 1

This is most likely being caused because you are joining on two columns of different data type (one nvarchar the other numeric) - your nvarchar probably contains numbers containing characters that won't convert to decimal (e.g. ',' or '.').