Records are getting rejected

[pavankatra]

hi,
my job design is 2 datasets------>join--------->Transformer---------->oracle

totally ihave 29 columns to insert.if i used 28 columns its going fine,whenever i used that 29th column then records are getting rejected.its nullable column.
so i hard coded that column with value which is coming from source that time also its rejected,now i removed some data from that column then its inserted.below are the used logics and hardcoded values
this logic i have used in the job
If (IsNull(lk_trim_trans.doc_notes)) Then TrimLeadingTrailing(lk_trim_trans.doc_notes) Else TrimLeadingTrailin(lk_trim_trans.doc_notes)
if i use above logic records are getting rejected.
source value is
'Index 18472 Sheet Ref 03.05.10; http://www.vishakaexp.co.in/Publication ... 05031020/0;'
now also its rejected

i have modified like this
'Index 18472 Sheet Ref 03.05.10; http://www.vishakaexp.co.in/Publications/2010/03/;'
now its inserted.

Could anyone please help me to sortout this issue.Thanks in advance.

[kris007]

If (IsNull(lk_trim_trans.doc_notes)) Then TrimLeadingTrailing(lk_trim_trans.doc_notes) Else TrimLeadingTrailin(lk_trim_trans.doc_notes)

What you are doing here is trying to Trim the NULL which is not possible. Your transform logic should be

Code: Select all

If (IsNull(lk_trim_trans.doc_notes)) Then SetNull() Else TrimLeadingTrailing(lk_trim_trans.doc_notes)

Try that and let us know if it helps.

[pavankatra]

If (IsNull(lk_trim_trans.doc_notes)) Then TrimLeadingTrailing(lk_trim_trans.doc_notes) Else TrimLeadingTrailin(lk_trim_trans.doc_notes)

What you are doing here is trying to Trim the NULL which is not possible. Your transform logic should be

Code: Select all

If (IsNull(lk_trim_trans.doc_notes)) Then SetNull() Else TrimLeadingTrailing(lk_trim_trans.doc_notes)

Try that and let us know if it helps.

thanks for your suggestion.actually problem is not with null,problem is with data.if i am trying to insert the data coming from the source its not inserting,if we truncate the data then its working fine.

[ArndW]

Add a peek output stage in addition to your Oracle stage. Identify a row that isn't working correctly, and see what the contents are in the Peek stage. Is the string too large? Nulls in other columns? Key constraints in the Database?

[pavankatra]

Add a peek output stage in addition to your Oracle stage. Identify a row that isn't working correctly, and see what the contents are in the Peek stage. Is the string too large? Nulls in other columns? Key constraints in the Database?

Thanks for your reply.
doc_notes column datatype in database is varchar2(1000),in datastage its not accepting 1000 for varchar.
i made it to longvarchar that is the problem.
now i made it to varchar 999.so ita working fine.
thanks alot for your help.

[kris007]

You would still need to correct your transform to properly handle the NULL's and avoid rejects in the future when the data comes in with NULL values.