Records getting rejected
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 5
- Joined: Thu Sep 03, 2009 12:08 pm
Records getting rejected
I am using ODBC_Enterprise stage to pull data from database - SQL Server to the target stage Oracle_Enterprise Database - Oracle. I have over 20,000 records to pull from source to target. There are 2 records getting rejected with sqlcode:-1438 .
I am suspecting this is a data size/precision issue. I am mapping data from datatype - money size - 8 in SQLServer to Float in Oracle. I have size big enough to accomodate the value in Oracle. When I do a direct insert with the same value in Oracle it works fine but fails using the datastage Any help will be appreciated
I am suspecting this is a data size/precision issue. I am mapping data from datatype - money size - 8 in SQLServer to Float in Oracle. I have size big enough to accomodate the value in Oracle. When I do a direct insert with the same value in Oracle it works fine but fails using the datastage Any help will be appreciated
-
- Premium Member
- Posts: 45
- Joined: Fri Nov 07, 2008 12:22 pm
-
- Participant
- Posts: 5
- Joined: Thu Sep 03, 2009 12:08 pm
-
- Premium Member
- Posts: 45
- Joined: Fri Nov 07, 2008 12:22 pm
-
- Participant
- Posts: 5
- Joined: Thu Sep 03, 2009 12:08 pm
Actually, that's quite a bit of help. What is means is that a number in one of those records is too big for the decimal mask you are using. For example, you are trying to write out 12345.78 in a Decimal 6,2 field. Check out some of the larger numbers against the masks you are using. If you need help, look for some previous posts I know some of us have written on sizing decimal masks.
Note: Do not assume that DataStage and Oracle use exactly the same syntax when specifying decimal masks...
Note: Do not assume that DataStage and Oracle use exactly the same syntax when specifying decimal masks...
-
- Participant
- Posts: 5
- Joined: Thu Sep 03, 2009 12:08 pm
The 2 records that are getting rejected are the ones that have largest values in the amount column.
I used in my target Oracle database and the target stage Float(25,5)
The value I have in my source database SQL Server(money is 1000000000.0000
All the records(including the 2 reject records) passes though all the intermediate stages but gets rejected at the target Oracle_enterprise stage.
I used in my target Oracle database and the target stage Float(25,5)
The value I have in my source database SQL Server(money is 1000000000.0000
All the records(including the 2 reject records) passes though all the intermediate stages but gets rejected at the target Oracle_enterprise stage.
-
- Premium Member
- Posts: 45
- Joined: Fri Nov 07, 2008 12:22 pm
Even if you define your column as a Float(25,5) in your target Oracle database stage, your database will reject out the rows. In order for these two rows to land in your database, you will have to change the properties (meaning make your column a Float(25,5)) in your Database.Scorpio007 wrote:I used in my target Oracle database and the target stage Float(25,5)
Hope this helps.
-Hiral
Thanks,
Hiral Chauhan
Hiral Chauhan
-
- Participant
- Posts: 5
- Joined: Thu Sep 03, 2009 12:08 pm
-
- Premium Member
- Posts: 45
- Joined: Fri Nov 07, 2008 12:22 pm
hmmmm. Sorry about that..
I would probably analyze those two records on the source side then. I would try to find out what makes them different than the rest. We had that kind of issue once and it turned out that we had some hex characters in our incoming record that made the target field bigger than what it was. May be you want to send the rejected records into a sequential file, download that file as a txt and view it under a hex editor.
Have you tried to see if those records make it to a varchar? What happens if you tried to bring it through just as text?
I would probably analyze those two records on the source side then. I would try to find out what makes them different than the rest. We had that kind of issue once and it turned out that we had some hex characters in our incoming record that made the target field bigger than what it was. May be you want to send the rejected records into a sequential file, download that file as a txt and view it under a hex editor.
Have you tried to see if those records make it to a varchar? What happens if you tried to bring it through just as text?
Thanks,
Hiral Chauhan
Hiral Chauhan