DSXchange

Hi all,

I am trying to avoid null values for Name field, I am doing simple transformation from oracle database to another oracle database through an ODBC-->TRF-->ODBC.

can anyone suggest me to avoid null values in NAME Field(Varchar).

Thanks in Advance

You didnt specify your requirement clearly, Avoiding Null in Names when writing to target or selecting from Source..

If it is target, In the transformer, for the name column write the derivation like If Len(Trim(InputLink.Name)) = 0 Then 'DEFAULT' Else InputLink.Name.

If it is in source, select * from Table where NAME IS NOT NULL

Write a constraint:

Trim(Source.column_name) <> ''

Good luck

"Avoid" how? Don't insert the record? Convert the null to something else? First task is detection with IsNull (typically) then either constrain or transform based on the result.

pxraja wrote:Hi all,

I am trying to avoid null values for Name field, I am doing simple transformation from oracle database to another oracle database through an ODBC-->TRF-->ODBC.

can anyone suggest me to avoid null values in NAME Field(Varchar).

Thanks in Advance

Try giving in constraint in the transformer the following piece of code

Not(isNull(Trim(Namefield)) and Trim(Namefield)<>""

Avoid = don't select them in the first place (as DS_SUPPORT suggested). If you still need the rows in which NAME is null, perhaps your query could convert NULL to an in-band value, or you can do this within the job design.

Hi all

Thanks for your suggestions, I dont want to pass the NULL value to the target because target column is key column. I had created target database in oracle whose key column is NOT NULL and its a primary key.

then i simply transformed the input link to output. Its not accepting the NULL (obviously) and I am getting REJECTED rows which is having NULL values. Is this approach is GOOD if the source is going to be large

Hi all

Thanks for your suggestions, I dont want to pass the NULL value to the target because target column is key column. I had created target database in oracle whose key column is NOT NULL and its a primary key.

then i simply transformed the input link to output. Its not accepting the NULL (obviously) and I am getting REJECTED rows which is having NULL values. Is this approach is GOOD if the source is going to be large

Hi all

Thanks for your suggestions, I dont want to pass the NULL value to the target because target column is key column. I had created target database in oracle whose key column is NOT NULL and its a primary key.

then i simply transformed the input link to output. Its not accepting the NULL (obviously) and I am getting REJECTED rows which is having NULL values. Is this approach is GOOD if the source is going to be large

Hi,

Use NVL function in the query itself if u want substitute any value for null.The other way we have is If(Len(Trim(field)) returns true if the field is not null.(in datastage transformer)

pxraja wrote:then i simply transformed the input link to output. Its not accepting the NULL (obviously) and I am getting REJECTED rows which is having NULL values. Is this approach is GOOD if the source is going to be large

No, this is never a "good" approach. Use a constraint to ensure these rows never go to the database. Up to you where they do go, including nowhere.

Hi all,

Thanks for your suggestions, I had used the constraint and its working.