Hi all.
ray.wurlod wrote:It seems to me that 62.95081967 has more than five decimal places.
What is the defined data type in SQL Server? Did you import the table definition from SQL Server into DataStage, and use those me ...
I think the problem is not due to the number of decimal places, because I've also tried fixed values with less ones.
chulett wrote:
I'd still suggest you try reading it as a string and then use a custom insert statement that does the appropriate cast, see if that works.
I tried to use "varchar" both on input and output stage, using the default "Insert new or update existing rows", and I get the followint 2 errors in the director
(1)
CopyOfJSGRV_FATTVASIP_00300_FATTURE_MESE_LOADDB1..ODBC_ORDER.ORDERDETAIL_insupd: DSD.BCIOpenW results of function SQLColAttributes(MVPREZZO) gave MetaData mismatch
MetaData mismatch on COLUMN.SCALE Expected = 0 Actual = 5
because varchar is diferent from decimal(18,5) that is the imported one.
(2)
CopyOfJSGRV_FATTVASIP_00300_FATTURE_MESE_LOADDB1..Transformer_253.ORDERDETAIL_insupd: DSD.BCIPut call to function SQLExecute failed.
SQL statement:UPDATE SE_ORDERDETAIL SET XORDERID = ?, MVCODICE = ?, MVDESART = ?, MVPREZZO = ?, MVCODIVA = ?, CPCCCHK = ? WHERE (XORDERDETAILID = ?)
SQLSTATE=22005, DBMS.CODE=0
[DataStage][SQL Client]Non-numeric data was found where numeric required
XORDERDETAILID = "SGR000000001"
XORDERID = "SGR000000001"
MVCODICE = "CALD_MANUTENZIONE"
MVDESART = "Manutenzione Caldaia"
MVPREZZO = 62.95081967
MVCODIVA = "22"
CPCCCHK = "XXX"
Since the value in the csv file is "62.95081967" I suppose Datastage is not doing a conversion, and store this value in director.
I suppose ODBC is doing a conversion and it's passing a different value to the db server.
In fact, launching the followig sql in a sql pad, with the number enclosed with "'"
insert into SE_ORDERDETAIL (
XORDERDETAILID,
XORDERID,
MVPREZZO,
CPCCCHK
) values(
'SGR000000001',
'SGR000000001',
'13.4',
'XXX'
)
the value is correctly inserted.
I tried to user "User-defined SQL" tryng t put "'" around the values
INSERT INTO SE_ORDERDETAIL(XORDERDETAILID, XORDERID, MVCODICE, MVDESART, MVPREZZO, MVCODIVA, CPCCCHK) VALUES (?,?,?,?,'?',?,?);
it doesn't work..and I get the error
CopyOfJSGRV_FATTVASIP_00300_FATTURE_MESE_LOADDB1..Transformer_253.ORDERDETAIL_insupd: DSD.BCIPut call to function SQLExecute failed.
SQL statement:INSERT INTO SE_ORDERDETAIL(XORDERDETAILID, XORDERID, MVCODICE, MVDESART, MVPREZZO, MVCODIVA, CPCCCHK) VALUES (?,?,?,?,'?',?,?)
SQLSTATE=37000, DBMS.CODE=8114
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to numeric.
XORDERDETAILID = "SGR000000001"
XORDERID = "SGR000000001"
MVCODICE = "CALD_MANUTENZIONE"
MVDESART = "Manutenzione Caldaia"
MVPREZZO = "62.95081967"
MVCODIVA = "22"
CPCCCHK = "XXX"
It's a very big problem I'm not able to insert this value in the db.
Please, let me know how to do this
chulett wrote:I'd still suggest you try reading it as a string and then use a custom insert statement that does the appropriate cast, see if that works.
Thanks,
Andrea