CODEWITHSUNDEEP
sqlibm-midrange
Richipal
Richipal

Reputation: 731

Conversion error on host variable or parameter *N

I am getting this Error on Insert statement to AS400 database, using Java with JDBC.

Upvotes: 7

Views: 21569

Answers (4)

eaglei22
eaglei22

Reputation: 2831

My issue was I overlooked the fact that I had single quotes around my parameter place holder. So I was getting a, 6 -- Numeric data that is not valid. error.

So, for example:

select someColumn
From   favTable
where  someOtherColumn = '?'

command.Parameters.Add("decVal", OleDbType.Decimal, 10).Value = model.someDecVal;

Correcting to:

select someColumn
From   favTable
where  someOtherColumn = ?

solved my issue.

Looks easy to catch here, but with big queries, this can be easily overlooked. Wasted about an hour on this.

Upvotes: 0

Hambone
Hambone

Reputation: 16387

I got this error today running the following query:

select *
from mytable
where invoice_date >= '2019-01-01'

Turned out invoice "date" is not a date... it's a decimal that impersonates a date. Poor database design, perhaps, but nonetheless the simple fix was:

select *
from mytable
where invoice_date >= 20190101

Upvotes: 1

user2208599
user2208599

Reputation: 11

I ran in to this issue when a table was updated from 2 digits to 3 for user ID's and the query inserts in to a history table where the history table was not updated to 3 digits for the user id. Used Alter Table to correct the history table and all is well.

alter table "Table Name" ALTER COLUMN "COLUMN NAME" SET DATA TYPE NUMERIC(3)

The error message is not intuitive:

Conversion error on variable or parameter *N (#-302) [IBM][System iAccess ODBC Driver][DB2 for i5/OS]SQL0012 - Correlation without qualification occurred for column "columnname" to table "Tablename". (#12)

Upvotes: 1

Richipal
Richipal

Reputation: 731

I figured it out, I had a numeric field with length 4 and decimal digits 2, In my insert I was trying to insert 100 in this numeric field which gave this error.

I altered the table and made it numeric with length 9 and decimal digits 2 
and the insert worked.

even though the error says "Conversion error", actually its the wrong field length.

Upvotes: 7

Related Questions