Hi Nuno
Do you try to use the Right Outer Join ?
regards
Rui Soares
Rui Soares
NOVABASE - Data Quality - Mi
Tlm : +351 . 96 347 0840
Mail : RUI.SOARES@NOVABASE.PT
-----Original Message-----
From: Nuno Pimenta [mailto:nuno.pimenta@tmn.pt]
Sent: Sexta-feira, 12 de Outubro de 2001 10:18
To: LIST-DataStage (E-mail)
Subject: Problem with join
Hi all,
I have the following problem that i dont know how i can resolve.
I have a file (A) that contains numbers,
A ( may be 1000 records)
Key
----
1
2
4
...
and i have to join with a table (B) that have 5 millions of records.
B (5 millions of records)
Key Desc
-------------------
1 A
1 B
1 C
2 A
3 A
4 A
...
But that join may be return more that one record by Key and i want the all the records, but only one is returned.
So, i want to use A as source and B as reference for questions of performance, but, how can i obtain all the records in this scenario?
Result
Key Desc
--------------------
1 A
1 B
1 C
2 A
4 A
How can i achieve this result?
Thanks in advance,
Nuno Pimenta
Problem with join
Problem with join
Hi all,
I have the following problem that i dont know how i can resolve.
I have a file (A) that contains numbers,
A ( may be 1000 records)
Key
----
1
2
4
...
and i have to join with a table (B) that have 5 millions of records.
B (5 millions of records)
Key Desc
-------------------
1 A
1 B
1 C
2 A
3 A
4 A
...
But that join may be return more that one record by Key and i want the all the records, but only one is returned.
So, i want to use A as source and B as reference for questions of performance, but, how can i obtain all the records in this scenario?
Result
Key Desc
--------------------
1 A
1 B
1 C
2 A
4 A
How can i achieve this result?
Thanks in advance,
Nuno Pimenta
I have the following problem that i dont know how i can resolve.
I have a file (A) that contains numbers,
A ( may be 1000 records)
Key
----
1
2
4
...
and i have to join with a table (B) that have 5 millions of records.
B (5 millions of records)
Key Desc
-------------------
1 A
1 B
1 C
2 A
3 A
4 A
...
But that join may be return more that one record by Key and i want the all the records, but only one is returned.
So, i want to use A as source and B as reference for questions of performance, but, how can i obtain all the records in this scenario?
Result
Key Desc
--------------------
1 A
1 B
1 C
2 A
4 A
How can i achieve this result?
Thanks in advance,
Nuno Pimenta
If you have a relational source (like ODBC or UV), there is a lookup property that can be accessed from within the transformer stage with a right-click on the lookup links header. Pick Link Properties, and mark the checkbox "reference link with multi row result set". For every row selected in the lookup SQL, there will be an output row, so your lookup for Key "1" will result in 1/A, 1/B, and 1/C outputs.
-----Original Message-----
From: Nuno Pimenta [mailto:nuno.pimenta@tmn.pt]
Sent: Friday, October 12, 2001 4:18 AM
To: LIST-DataStage (E-mail)
Subject: Problem with join
Hi all,
I have the following problem that i dont know how i can resolve.
I have a file (A) that contains numbers,
A ( may be 1000 records)
Key
----
1
2
4
...
and i have to join with a table (B) that have 5 millions of records.
B (5 millions of records)
Key Desc
-------------------
1 A
1 B
1 C
2 A
3 A
4 A
...
But that join may be return more that one record by Key and i want the all the records, but only one is returned.
So, i want to use A as source and B as reference for questions of performance, but, how can i obtain all the records in this scenario?
Result
Key Desc
--------------------
1 A
1 B
1 C
2 A
4 A
How can i achieve this result?
Thanks in advance,
Nuno Pimenta
-----Original Message-----
From: Nuno Pimenta [mailto:nuno.pimenta@tmn.pt]
Sent: Friday, October 12, 2001 4:18 AM
To: LIST-DataStage (E-mail)
Subject: Problem with join
Hi all,
I have the following problem that i dont know how i can resolve.
I have a file (A) that contains numbers,
A ( may be 1000 records)
Key
----
1
2
4
...
and i have to join with a table (B) that have 5 millions of records.
B (5 millions of records)
Key Desc
-------------------
1 A
1 B
1 C
2 A
3 A
4 A
...
But that join may be return more that one record by Key and i want the all the records, but only one is returned.
So, i want to use A as source and B as reference for questions of performance, but, how can i obtain all the records in this scenario?
Result
Key Desc
--------------------
1 A
1 B
1 C
2 A
4 A
How can i achieve this result?
Thanks in advance,
Nuno Pimenta