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.
Records are getting rejected
Moderators: chulett, rschirm, roy
Re: Rejecords are getting rejected
What you are doing here is trying to Trim the NULL which is not possible. Your transform logic should bepavankatra wrote: If (IsNull(lk_trim_trans.doc_notes)) Then TrimLeadingTrailing(lk_trim_trans.doc_notes) Else TrimLeadingTrailin(lk_trim_trans.doc_notes)
Code: Select all
If (IsNull(lk_trim_trans.doc_notes)) Then SetNull() Else TrimLeadingTrailing(lk_trim_trans.doc_notes)
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
-
- Participant
- Posts: 86
- Joined: Wed Mar 03, 2010 3:09 am
Re: Rejecords are getting rejected
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.kris007 wrote:What you are doing here is trying to Trim the NULL which is not possible. Your transform logic should bepavankatra wrote: If (IsNull(lk_trim_trans.doc_notes)) Then TrimLeadingTrailing(lk_trim_trans.doc_notes) Else TrimLeadingTrailin(lk_trim_trans.doc_notes)
Try that and let us know if it helps.Code: Select all
If (IsNull(lk_trim_trans.doc_notes)) Then SetNull() Else TrimLeadingTrailing(lk_trim_trans.doc_notes)
-
- Participant
- Posts: 86
- Joined: Wed Mar 03, 2010 3:09 am
Thanks for your reply.ArndW wrote: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?
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.
Re: Rejecords are getting rejected
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.
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson