Cross Join

[pramod_au]

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

[asnagaraj]

ask a new question after you understand what the problem is

Programmer Analyst,
Cognizant,
Chennai.

[pramod_au]

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

[vmcburney]

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

[ray.wurlod]

Or, as a REALLY slow solution, use an ODBC driver for text files. [xx(]

[pramod_au]

Hi ManjulaMadhu

F1,F2....Fn are the fields . dont relate the field to the ouput file.

Is there any solution other than custom SQL.

Regards
D.Pramod


Pramod

[vmcburney]

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