How to reject the rows that not in the parent table???
Moderators: chulett, rschirm, roy
How to reject the rows that not in the parent table???
Hi All,
Could u pls help me this?
I would like to reject the records those are not in the parent table.
Currently I am doing as follows.
I look up the parent table.Then I gave the constraint in the transformer as imp_main_table.NOTFOUND to reject the rows for reject link.
For input link, I gave the constraint as not(imp_main_table.NOTFOUND).
But still cannot reject the rows and those records run in the database and get the warning msg.
May I know whether I can use NOTFOUND cmd to reject the rows or not?
May I know how to make the incorrect rows to be rejected???
Appreciate any suggestion.
Help me pls...........
Could u pls help me this?
I would like to reject the records those are not in the parent table.
Currently I am doing as follows.
I look up the parent table.Then I gave the constraint in the transformer as imp_main_table.NOTFOUND to reject the rows for reject link.
For input link, I gave the constraint as not(imp_main_table.NOTFOUND).
But still cannot reject the rows and those records run in the database and get the warning msg.
May I know whether I can use NOTFOUND cmd to reject the rows or not?
May I know how to make the incorrect rows to be rejected???
Appreciate any suggestion.
Help me pls...........
Dear Cristina,
I have been tested it.
But it didn't work
I did as follow.
1.In the lookup step,I check the lookup table.key is null set Err column to ''.
2.In the next step,I checked the Len(err)>0 and if >0 then reject .Else go to db link.
But it didn't work.
Even the row couldn't reject to the reject link
Is there any other suggestion pls???
I have been tested it.
But it didn't work
I did as follow.
1.In the lookup step,I check the lookup table.key is null set Err column to ''.
2.In the next step,I checked the Len(err)>0 and if >0 then reject .Else go to db link.
But it didn't work.
Even the row couldn't reject to the reject link
Is there any other suggestion pls???
Cr.Cezon wrote:You can use the isnull function.
in a lookup, if the row doesn't mach the condition, the fields of the row are set to null.
regards,
Cristina
Oop!!!
I am sorry.
I put the Err='Error' if the reference key is null.
So I check the len(Err)>0 then reject the row.
Ok.I will try and let u know
Thanks for your suggestion.
I am sorry.
I put the Err='Error' if the reference key is null.
So I check the len(Err)>0 then reject the row.
Ok.I will try and let u know
Thanks for your suggestion.
Cr.Cezon wrote:if you put the err variable = ""
then the len(err) = 0 if you want to reject the row ,isn't it?
I think you must use if len() = 0 instead of len> 0 to reject.
any suggestion:
whay don't you use in the constraint:
isnull(field) --> then set the reject row option = true
regards,
Cristina.
ICE, you've never said what your lookup stage is. The Link Variable NOTFOUND works fine with a hashed file lookup, but I've found it... unreliable... with a database stage. Hence the advice to fall back on the Old School technique of checking the return value of one of your keys for null.
However, even that may not work if you are using custom SQL in the stage unless you match your check to whatever you return for a 'miss'.
However, even that may not work if you are using custom SQL in the stage unless you match your check to whatever you return for a 'miss'.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: How to reject the rows that not in the parent table???
TRY THIS.
IF ISNULL(TRIM(LINK.FIELDNAME)) OR TRIM(LINK.FIELD) = '' THEN @TRUE ELSE @FALSE
IF ISNULL(TRIM(LINK.FIELDNAME)) OR TRIM(LINK.FIELD) = '' THEN @TRUE ELSE @FALSE
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: How to reject the rows that not in the parent table???
Efficiency Noteadams06 wrote:TRY THIS.
IF ISNULL(LINK.FIELDNAME) OR TRIM(LINK.FIELD) = '' THEN @TRUE ELSE @FALSE
Code: Select all
ISNULL(TRIM(LINK.FIELDNAME)) OR TRIM(LINK.FIELD) = ''
IF test_expression THEN @TRUE ELSE @FALSE is inherently redundant.
Similarly, trimming NULL has no effect.
Last edited by ray.wurlod on Mon Mar 26, 2007 5:25 pm, edited 1 time in total.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Efficiency Noteravibabu wrote:Can you use MERGE or JOIN stage .That can be helpful to you.
through that stages you can remove what you want?the columns which are not matching that can be reject .
This approach involves the not inconsiderable overhead of converting from a server job to a parallel job. Server jobs do not have a Join stage, and the Merge stage in server jobs is to perform joins between text files.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Test the returned key column, or any returned not null column, to determine whether it is null. That is all you need. If it is null, the lookup failed. Your constraint expression on the "good rows" output should therefore be
Code: Select all
Not(IsNull(parent_table_link.key_column))
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.
Btw,
I realise that DSJ reject the rows to the reject file itself without giving any constraint when I fetch to the table.But it's not reliable I think coz I have found this kind of problem before.
Please confirm me that I am correct or not.
Before this I think I must give the constraint to reject the rows.
Thanks and Regards,
ICE
I realise that DSJ reject the rows to the reject file itself without giving any constraint when I fetch to the table.But it's not reliable I think coz I have found this kind of problem before.
Please confirm me that I am correct or not.
Before this I think I must give the constraint to reject the rows.
Thanks and Regards,
ICE
With the ODBC you won't be able to use the NOTFOUND link variable.
You typically give a constraint expression if you want a 'logical' reject - meaning, you want to avoid sending a row to the target that you know will cause a problem. A 'physical' reject doesn't need a constraint as it can catch records rejected by errors in the target stage just by checking the 'Reject Row' box. Of course, it's more complicated than that.
As a rule, I don't use the 'Reject Row' functionality because it logs a warning in the job, but prefer using specific constraints to do the dirty work - typically link_name.REJECTEDCODE.
You typically give a constraint expression if you want a 'logical' reject - meaning, you want to avoid sending a row to the target that you know will cause a problem. A 'physical' reject doesn't need a constraint as it can catch records rejected by errors in the target stage just by checking the 'Reject Row' box. Of course, it's more complicated than that.
As a rule, I don't use the 'Reject Row' functionality because it logs a warning in the job, but prefer using specific constraints to do the dirty work - typically link_name.REJECTEDCODE.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers