Hello
Is there any tool in DataStage6.0 to get cartesian product ?
for Input file F1 is a non-key column.(None of the other fields in Input file could be related to the fields in Lookup File)
For lookup file F1 is the key.
Input file Lookup File output file shuld contain
F1 F1 F2 F3 F1 F2 F3
== ========= =========
a K1 a Des1 K1 a Des1
b K2 a Des2 K2 a Des2
K3 a Des3 K3 a Des3
K4 b Des4 K4 b Des4
K5 c Des5
K6 c Des6
Pramod
Cross Join
Moderators: chulett, rschirm, roy
Hello
The files header got trimmed in my previous post.Sorry!!
refer to the my previous post for Field specifications.
Input file
F1
==
a
b
Lookup File
F1 F2 F3
=========
K1 a Des1
K2 a Des2
K3 a Des3
K4 b Des4
K5 c Des5
K6 c Des6
output file
F1 F2 F3
=========
K1 a Des1
K2 a Des2
K3 a Des3
K4 b Des4
Pramod
The files header got trimmed in my previous post.Sorry!!
refer to the my previous post for Field specifications.
Input file
F1
==
a
b
Lookup File
F1 F2 F3
=========
K1 a Des1
K2 a Des2
K3 a Des3
K4 b Des4
K5 c Des5
K6 c Des6
output file
F1 F2 F3
=========
K1 a Des1
K2 a Des2
K3 a Des3
K4 b Des4
Pramod
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
This is a join that is easily executed in a database. You may be best off loading your input file and your lookup file into two Universe tables (hash files) and using a Universe stage with custom SQL to join them.
The custom SQL would look something like this:
select lookuptable.f1, inputtable.f1, lookuptable.f3
from lookuptable, inputtable
where inputtable.f1 = lookuptable.f2
Vincent McBurney
Data Integration Services
www.intramatix.com
The custom SQL would look something like this:
select lookuptable.f1, inputtable.f1, lookuptable.f3
from lookuptable, inputtable
where inputtable.f1 = lookuptable.f2
Vincent McBurney
Data Integration Services
www.intramatix.com
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
You could write it in a routine using the sequential file commands such as openseq. You would open the input file in an outer loop, within an inner loop you would cycle through the lookup file until you found a match, you would then output a row for each match within the inner loop.
Vincent McBurney
Data Integration Services
www.intramatix.com
Vincent McBurney
Data Integration Services
www.intramatix.com