Page 1 of 2
How to reject the rows that not in the parent table???
Posted: Mon Mar 26, 2007 2:15 am
by ICE
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...........
Posted: Mon Mar 26, 2007 2:19 am
by Cr.Cezon
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
Posted: Mon Mar 26, 2007 2:44 am
by ICE
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???
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
Posted: Mon Mar 26, 2007 3:06 am
by Cr.Cezon
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.
Posted: Mon Mar 26, 2007 4:57 am
by ICE
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.
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.
Posted: Mon Mar 26, 2007 6:39 am
by chulett
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'.
Re: How to reject the rows that not in the parent table???
Posted: Mon Mar 26, 2007 10:32 am
by adams06
TRY THIS.
IF ISNULL(TRIM(LINK.FIELDNAME)) OR TRIM(LINK.FIELD) = '' THEN @TRUE ELSE @FALSE
Posted: Mon Mar 26, 2007 10:59 am
by ravibabu
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 .
Re: How to reject the rows that not in the parent table???
Posted: Mon Mar 26, 2007 5:23 pm
by ray.wurlod
adams06 wrote:TRY THIS.
IF ISNULL(LINK.FIELDNAME) OR TRIM(LINK.FIELD) = '' THEN @TRUE ELSE @FALSE
Efficiency Note
Code: Select all
ISNULL(TRIM(LINK.FIELDNAME)) OR TRIM(LINK.FIELD) = ''
performs the same task.
IF test_expression THEN @TRUE ELSE @FALSE is inherently redundant.
Similarly, trimming NULL has no effect.
Posted: Mon Mar 26, 2007 5:24 pm
by ray.wurlod
ravibabu 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 .
Efficiency Note
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.
Posted: Mon Mar 26, 2007 5:24 pm
by chulett
ravibabu wrote:Can you use MERGE or JOIN stage .That can be helpful to you.
Not in a Server job.
Posted: Mon Mar 26, 2007 5:27 pm
by ray.wurlod
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))
Posted: Mon Mar 26, 2007 7:43 pm
by ICE
Dear All,
I really appreciate you all advice.
I will try again and let u know the result.
Dear Chulett,
I am using the odbc stage.Not the hash file stage.
Thank u all.
ICE
Posted: Mon Mar 26, 2007 7:48 pm
by ICE
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
Posted: Mon Mar 26, 2007 8:17 pm
by chulett
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.