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

[Fawlty]

[quote="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...........[/quote][b][/b]

:)
Hi guess this

1) NOT(<inputlink>.NOTFOUND)= FOUND
2) so use <inputlink>.NOtFOUND=NOTFOUND

use second one and select reject option

[oacvb]

Lookup is ODBC stage, How about source? Is it again ODBC?If so, can you join both table in source ODBC through SQL query (Make sure performance is not affected), if so no need to have the look up.

[ICE]

Dear Chulett,

Thank you so much for your explanation and advise.
Now I use hash file stage between the ODBC and Tranformer according to your advice and so it works now.
My problem has been solved
Thanks so much for all of u.

But I would like to confirm with u.

1.As per ur explanation ,I understand the function NOTFOUND can be used only for hash file?That means it cannot be work with any other stage like ODBC?
2.Do u mean the 'Reject Row' is DSJ internal reject function?I think it's an internal reject function.Right?It gives warning msg in the DSJ log when it reject the rows.

Btw,I have one more question.How can I see the DSJ old logs those has not been cleared??? We can see only recent 100 logs in DSJ director.Right? May I know how to manage to see the old logs???


Thanks and Regards,

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.

[chulett]

1) You can use the NOTFOUND variable for anything you like. I'm just passing along my own personal observance that while I know it works fine for Hashed File stages, I found it unreliable for DBMS work. Your mileage may vary.

2) Not sure what you mean. Yes, that functionality is built into the engine and yes, it will automatically log a warning with the total number of rows rejects. I don't believe in warnings, so don't use that functionality.

3) The default filter when viewing logs in the Director is the last 100 entries. Change it. You can make it whatever you want - either a bigger number or unlimited. Be careful with the later, sometimes big is too big.

[ICE]

Hi Chulett,

May I know where can I change the log entry?In Director?
Thank you so much for your advice.

ICE

1) You can use the NOTFOUND variable for anything you like. I'm just passing along my own personal observance that while I know it works fine for Hashed File stages, I found it unreliable for DBMS work. Your mileage may vary.

2) Not sure what you mean. Yes, that functionality is built into the engine and yes, it will automatically log a warning with the total number of rows rejects. I don't believe in warnings, so don't use that functionality.

3) The default filter when viewing logs in the Director is the last 100 entries. Change it. You can make it whatever you want - either a bigger number or unlimited. Be careful with the later, sometimes big is too big.

[chulett]

From the Director's Log View, there should be a View entry on the toolbar, and a Filter option under it. From what I recall. It should be fairly obvious but you must be viewing the log for it to show up, or at least that's how I'm remembering it right now.

[ray.wurlod]

You can also click on the "open book" tool on the toolbar, choose Log from the View menu, or right click on the job and choose View Log from than menu.
Invoke the filter using Ctrl-T or from the right click menu.

[ICE]

Thanks for your explanation.........

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.