Page 1 of 2
How to get matched records between two columns
Posted: Wed Sep 17, 2008 12:23 am
by 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
Posted: Wed Sep 17, 2008 12:32 am
by ray.wurlod
What have you tried? What are you considering? Hint: how would you do it without DataStage?
Posted: Wed Sep 17, 2008 12:59 am
by 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.
Posted: Wed Sep 17, 2008 10:41 pm
by laknar
Use NOT in Query to select unmatched rows.
(or)
Check for Null values in Transformer and then populate
Posted: Wed Sep 17, 2008 11:08 pm
by ray.wurlod
Think JOIN STAGE
Posted: Wed Sep 17, 2008 11:53 pm
by 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.
Posted: Thu Sep 18, 2008 12:02 am
by 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.
Posted: Thu Sep 18, 2008 7:41 am
by 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!
Posted: Thu Sep 18, 2008 3:41 pm
by ray.wurlod
I don't propose to dismount.
Posted: Thu Sep 18, 2008 5:38 pm
by etldeveloper2050
Yeah correct... dummy should be a not null field.. forgot to mention that :D
Posted: Fri Sep 19, 2008 10:31 pm
by ambasta
Use a lookup stage and set the property to reject ...Capture the rejected record and use it ....
Posted: Sun Sep 21, 2008 2:48 am
by 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?
Posted: Sun Sep 21, 2008 2:50 am
by 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?
Posted: Tue Sep 30, 2008 9:38 am
by abc123
Do an inner join like Ray said and insert the rejected records.
Re: How to get matched records between two columns
Posted: Fri Oct 03, 2008 6:52 pm
by Kris123
praburaj wrote: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.