rejecting rows while updating in oracle

[kirankota79]

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

[rajngt]

Is the column has been marked as null

[kirankota79]

they are marked as not nullable

[ArndW]

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.

[chulett]

Unless you want to preserve the current content of those columns, in which case you'd need to get them from a lookup first and supply them when you don't have new data coming in.

[kirankota79]

is it enough that we change the definition on the datastage side or do we need to do on the database side too?

thanks

[ArndW]

DataStage and DataBase metadata should always be identical. When it comes to nullability settings, the two need to be identical.

[kirankota79]

we cannot change any properties on the database side, is that anything i can do from datastage side?

[Krazykoolrohit]

we cannot change any properties on the database side, is that anything i can do from datastage side?

then, Populate a default value for all rows whenever you have blank or a null value and then load it into oracle.

[ArndW]

You can do this in the job, but you need to explain

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.

How are you looking for values? In a transform stage?
Where is the record rejected, in your transform or in the write. If in the write, why is it rejecting the record?

[Minhajuddin]

they are marked as not nullable

Are these "5 columns" not nullable even in your database?
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.

[kirankota79]

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

[ArndW]

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.

[ray.wurlod]

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.

[kirankota79]

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.