How to get matched records between two columns

[praburaj]

Hi,

I have two columns in my source table Key_Pln_N and key_pln and my reference table column is key_pln_n1. now, i want to compare with key_pln_n1 and Key_Pln_N. If there is any unmatched records between these two columns i have to populate those records in to my dataset. Plz help me to resolve this problem

[ray.wurlod]

What have you tried? What are you considering? Hint: how would you do it without DataStage?

[praburaj]

I am doing in datastage only. My source is dataset which is having 6 records where key column is Key_Pln_N and my reference table is db2 which is having 29 records where key column is key_pln_n1. I want compare the records between these two columns. if records are unmatched between these two columns, i want to insert those unmatched records in my target table. I have created one stage variable there i have written query like this

stgvar:

If DSLink73.Key_Pln_N <> DSLink73.key_pln_n1 Then 1 Else 0

then constraints i written stavar=1.

But i got all records from the source dataset. I hope now i clearly explained my problem.

[laknar]

Use NOT in Query to select unmatched rows.
(or)
Check for Null values in Transformer and then populate

[ray.wurlod]

Think JOIN STAGE

[etldeveloper2050]

Hi,
You can do something like this:

1) get 1 as Dummy from the reference table.
2) Use Join stage: Left Outer Join. left link: input link from dataset and right link is input link from reference table.
3) Next use a filter stage and output records based on dummy<>1. All the unmatched records will have dummy=0.

I hope this solves your problem.

[ray.wurlod]

Ur was a city in ancient Babylonia.

The second person personal pronoun in its possesive form is spelled "your" in English.

Please strive for a professional standard of written English on DSXchange.

You would not use SMS-style abbreviations in your formal work documentation, would you?

You are communicating with the same group of people (potential future maintainers) when you post on DSXchange.

[OddJob]

For completeness, the nullability of the 'dummy' field affects the value it is set to after the join. If NULL is allowed, then the unmatched 'dummy' field will be set to NULL. If NULL is not allowed then the unmatched field will be 0.

Let's hope Ray didn't hurt himself when dismounting from his high horse!

[ray.wurlod]

I don't propose to dismount.

[etldeveloper2050]

Yeah correct... dummy should be a not null field.. forgot to mention that :D

[ambasta]

Use a lookup stage and set the property to reject ...Capture the rejected record and use it ....

[dsx999]

Before boarding on to this forum(or job), it is worth spending some time with documentation.

And even before that... be clear with you question... to avoid conflicting answers.

"How to get matched records between two columns?"

...conflicts with..

"... i want to insert those unmatched records in my target table. "

What do you want mate?

[dsx999]

Before boarding on to this forum(or job), it is worth spending some time with documentation.

And even before that... be clear with you question... to avoid conflicting answers.

"How to get matched records between two columns?"

...conflicts with..

"... i want to insert those unmatched records in my target table. "

What do you want mate?

[abc123]

Do an inner join like Ray said and insert the rejected records.

[Kris123]

Hi,

I have two columns in my source table Key_Pln_N and key_pln and my reference table column is key_pln_n1. now, i want to compare with key_pln_n1 and Key_Pln_N. If there is any unmatched records between these two columns i have to populate those records in to my dataset. Plz help me to resolve this problem

If I understand you correct, your source records are in a table.
If you are using Oracle or Teradata Database then you may use the respective Database Stage or the ODBC stage and do your Join query inside the stage sending the output link to your dataset.
If you are not sourcing a table then you could use the join stage.