how to avoid null value into columns
Moderators: chulett, rschirm, roy
how to avoid null value into columns
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
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
-
- Premium Member
- Posts: 232
- Joined: Fri Aug 04, 2006 1:20 am
- Location: Bangalore
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
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
Re: how to avoid null value into columns
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)<>""
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
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
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
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
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
-
- Participant
- Posts: 6
- Joined: Thu Aug 07, 2008 12:49 am
- Location: Chennai
handling null
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)
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)
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.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
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers