rejecting rows while updating in oracle
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
rejecting rows while updating in oracle
Hi,
I have this following scenario, I am updating 5 columns using a key column in a single update statement.
While one column has the values in all the rows and the other 4 columns has values sparsely. While updating it is looking for values in all the 5 columns of a particular row and if it doesn't find a value in a sigle column it is rejecting the entire row without updating. I want to update even it has a value in only one column of particular row?
thanks
I have this following scenario, I am updating 5 columns using a key column in a single update statement.
While one column has the values in all the rows and the other 4 columns has values sparsely. While updating it is looking for values in all the 5 columns of a particular row and if it doesn't find a value in a sigle column it is rejecting the entire row without updating. I want to update even it has a value in only one column of particular row?
thanks
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
Then you need to either modify the definitions on your table or add empty values for the sparsely populated columns. The easier method is to make these columns nullable, that way if you don't specify them on an insert they won't generate an error, just a null value.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
DataStage and DataBase metadata should always be identical. When it comes to nullability settings, the two need to be identical.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
-
- Charter Member
- Posts: 560
- Joined: Wed Jul 13, 2005 5:36 am
- Location: Ohio
Re: rejecting rows while updating in oracle
You can do this in the job, but you need to explain
Where is the record rejected, in your transform or in the write. If in the write, why is it rejecting the record?
How are you looking for values? In a transform stage?kirankota79 wrote:While updating it is looking for values in all the 5 columns of a particular row and if it doesn't find a value in a sigle column it is rejecting the entire row without updating.
Where is the record rejected, in your transform or in the write. If in the write, why is it rejecting the record?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
Are these "5 columns" not nullable even in your database?kirankota79 wrote:they are marked as not nullable
If they are not nullable in your database, how are you planning to insert NULLs (the database wouldn't accept this).
Do you want to convert them to some default value if you find a NULL?
Are these columns NULLABLE in the database?
Do the records which get rejected exist in the database?
The answers to these questions would lead to your solution.
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
i have created a job like following:
i/p stage (oracle) ---> transformer--->dataset (job1)
dataset -->Transformer-->o/p oracle stage (job2)
in the job1 for example i have columns ssn, address, city, postal, state i transform them to some value and writes to a dataset.
Then i use this dataset to update the same table with new values in the second job.
the problem is some of the columns (not nullable) in the table have some empty rows and when i transform them they have empty values in the dataset. for example state column has no values at all and i am trying to update. But may be in the future there will be some values.
i use the reject link i get the sqlcode - 1407
i/p stage (oracle) ---> transformer--->dataset (job1)
dataset -->Transformer-->o/p oracle stage (job2)
in the job1 for example i have columns ssn, address, city, postal, state i transform them to some value and writes to a dataset.
Then i use this dataset to update the same table with new values in the second job.
the problem is some of the columns (not nullable) in the table have some empty rows and when i transform them they have empty values in the dataset. for example state column has no values at all and i am trying to update. But may be in the future there will be some values.
i use the reject link i get the sqlcode - 1407
Since you cannot insert an SQL null into the columns, your second job needs to insert or update some value. Perhaps an empty string, or a string of "??????"s or something else - anything but the null.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Since it's an update, you only need to send those values which you have.
This may involve designing the job to take different paths for different combinations of changed columns.
This may involve designing the job to take different paths for different combinations of changed columns.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
ray: i am doing in your way...like i created 4 links from the transformer along with key columns for each column to update, instead of single link for all columns. The job starts without any problem and in the middle it is just hanging ...showing the lines in the blue color and nothing is happening. Is it because they are accessing the same key columns at a time?
how can i avoid this.
how can i avoid this.