I have found that when declaring columns on my source table as VARCHAR(200 BYTE) when I map them in the query and declare them as Int (declared as int in target target table) that in the optimized sql it would add a substring on the column. There was no substring in the query transform it just seemed to add it by itself.
The problem was fixed by changing the datatype of the target table to a decimal. But I was wondering if anyone had encountered this problem before and knew the reason for it, because at the moment we can not define any tables using integer.
If needed we are using an oracle 10g database and using Data Services XI 3.2
you sholud see substring in case the target column datatype is also varchar and its size is less than source column and complete operation is getting push down (INSERT INTO … SELECT)
let me check this, so if you change the target column datatype to DECIMAL then you do not see substring function in the optimized SQL ?
Between what is your Source DB and Target DB?
Also, we cannot get a pushdown-sql once the datatypes get changed. For a DF to show PushDown SQL, we need to have target mapping a mirror of the source.
So I tried with my source/target as SQL Server and DI 11.7.3
(BOB member since 2008-07-17)