How to reject the rows that not in the parent table???

[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...........

[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

[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???

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

[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.

[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.

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.

[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'.

[adams06]

TRY THIS.

IF ISNULL(TRIM(LINK.FIELDNAME)) OR TRIM(LINK.FIELD) = '' THEN @TRUE ELSE @FALSE

[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 .

[ray.wurlod]

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.

[ray.wurlod]

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.

[chulett]

Can you use MERGE or JOIN stage .That can be helpful to you.

Not in a Server job.

[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))

[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

[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

[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.