One Star eng
One Star

Cartesian product

Hi, how can I do to obtain a cartesian product between two oracle tables?I use tMap component. Thank you.
21 REPLIES
Employee

Re: Cartesian product

Hi,
A tOracleInput component is perhaps more adapted to your need! The tMap only accepts a main input flow and lookup tables.
One Star eng
One Star

Re: Cartesian product

Thanks for your answer. Yes, I use 2 tOracleInput (for 2 tables) and 1 tMap and 1 tLogRow, but when I try to obtain the cartesian product with the tMap I get this warning : "The lookup table 'row2' should have at least one expression key filled" and in the result the fields of the second table are NULL . I think there is not a join between the 2 tOracleInput. What can I do? You can look the jpg below
Employee

Re: Cartesian product

Hi,
What do you exactly mean by cartesian product ? My definition is :
SELECT *
FROM table1, table2

and I only need one tOracleInput component to produce it.
One Star eng
One Star

Re: Cartesian product

Hi, and sorry for my english Smiley Sad
yes, cartesian prodact is:
select *
from table1, table2
where table1.a > 10
yes, I can write this select in tOracleInput,
but in my case table1 and table2 are two big select and I already use 2 tOracleInput, and now I?d like to create a job design where I can unite, with a condition, some fields from ?tOracleInput1? and some field from ?tOracleInput2? in to new table. For ?unite? the fields, I use the tMap, so I can explain the "where" conditions, but the tmap can not combine any fields without any join between tOracleInput1 and tOracleInput2. Which components do I have to use? Can you help me Smiley Sad
Employee

Re: Cartesian product

Have you tried E.L.T. components such as tELTOracleInput, tELTOracleMap and tELTOracleOutput ?
You can see the pdf documentation on these components at this URL http://www.talend.com/tos/user-guide/V080/TalendOpenStudio_UG_0.8_Eng.pdf from page 130.
One Star eng
One Star

Re: Cartesian product

Have you tried E.L.T. components such as tELTOracleInput, tELTOracleMap and tELTOracleOutput ?
You can see the pdf documentation on these components at this URL http://www.talend.com/tos/user-guide/V080/TalendOpenStudio_UG_0.8_Eng.pdf from page 130.

Hi, thanks for your answer. I've read the pdf documentation and I tried E.L.T. components such as tELTOracleInput, tELTOracleMap and tELTOracleOutput, but with E.L.T components I can make cartesian product but I can?t write my ?select? in tELTOracleInput, so I think it's not good for me Smiley Sad
Employee

Re: Cartesian product

You can add where clauses in tELTOracleMap by pushing the button "Add filter row" in an output table.
One of the advantages of ELT components is performance because all processing are done by the Database server.
An other way is to use the tOracleRow. You can type your custom query and send results in a tOracleOutput component.
One Star eng
One Star

Re: Cartesian product

You can add where clauses in tELTOracleMap by pushing the button "Add filter row" in an output table.
One of the advantages of ELT components is performance because all processing are done by the Database server.
An other way is to use the tOracleRow. You can type your custom query and send results in a tOracleOutput component.

Hi and thanks for your answer.
I understand that
?add where clauses in tELTOracleMap by pushing the button "Add filter row" in an output table? Smiley Happy
but in tELTOracleMap I can?t write my select; Smiley Sad

and I understand that
?An other way is to use the tOracleRow. You can type your custom query and send results in a tOracleOutput component? Smiley Happy
but I don?t want to write a big cartesian product in 1 tOracleRow or 1 tOracleInput . Smiley Sad
Thanks a lot Smiley Happy
Employee

Re: Cartesian product

So, if I understand well, you would like to use a standalone component with a free field for typing your query such as
INSERT MyTable (MyColumn1, MyColumn2, MyColumn3, MyColumn4) (SELECT * FROM MyTable1, MyTable2)
Is it right ?
With Talend 2.0.0 you can execute this query in tOracleRow but you must connect a fake start component before to send one row, then the query will be executed.
With Talend 2.0.1 (not released yet) you will be able to use the tOracleRow alone.
One Star

Re: Cartesian product

Do you have some idea about the release plan ?
When version 2.0.1 will be released?
Thank you
One Star eng
One Star

Re: Cartesian product

So, if I understand well, you would like to use a standalone component with a free field for typing your query such as
INSERT MyTable (MyColumn1, MyColumn2, MyColumn3, MyColumn4) (SELECT * FROM MyTable1, MyTable2)
Is it right ?
With Talend 2.0.0 you can execute this query in tOracleRow but you must connect a fake start component before to send one row, then the query will be executed.
With Talend 2.0.1 (not released yet) you will be able to use the tOracleRow alone.

Yes J, to be more precise:
select SELECT1.A,SELECT2.B
from (select * from TABLE1) select1, (select * from TABLE2 where C='kk') select2
but select1 end select2 are the output of two distinct components!So, my problem is to find a graphic component where I can select ?A? and ?B? and to use the output to other component, like tMap!
I?d like to see a similar graphic:
---------------
| select1 |
--------------
| --------------
----------------->| cartesian |--------> ?.. other graphic....
----------------->| product |
| | and select |
| | A and B |
| --------------
---------------
| select2 |
----.----------
Is it possible? What do you think about?
Employee

Re: Cartesian product

The example on picture should fulfil your problem.
What do you think ?
One Star eng
One Star

Re: Cartesian product

The example on picture should fulfil your problem.
What do you think ?

Thanks a lot for your graphic and for you suggestion J
Yes, that you have done is that I?d like to do, but I can?t because I haven?t ?table1? and ?table2?! L I have two big distinct graphics created in Talend or two big distinct ?select?.
Well :
1. how can I write my two ?select? in your tETLOracleInput_1 and tETLOracleInput_2?
or
2. how can I connect my two distinct graphics to your tELTOracleInput_1 and tETLOracleInput_2?
I know that:
1. If I use tMap component then I can write my two big distinct ?select? into two tOracleInput, but I can?t obtain the ?cross Join? o ?cartesian product?
2. If I use tELTOracleMap I can make a ?cross Join? but I can?t connect anything to the tELTOracleMap or I can connect tETLOracleInput but I can?t write my select because is not allowed.
Is it true?
tnx
Employee

Re: Cartesian product

I have a doubt : do you really want a cartesian product between your 2 inputs ?
In a cartesian product, if your first input has X rows and your second input has Y rows, the output resulting from the cartesian product will have X*Y rows.
One Star eng
One Star

Re: Cartesian product

I have a doubt : do you really want a cartesian product between your 2 inputs ?
In a cartesian product, if your first input has X rows and your second input has Y rows, the output resulting from the cartesian product will have X*Y rows.

yes Smiley Happy I want
Employee

Re: Cartesian product

OK, the best way to do it is to load your 2 inputs in 2 Oracle tables and then use ELT components the way amaumont explained in his last post.
One Star eng
One Star

Re: Cartesian product

OK, the best way to do it is to load your 2 inputs in 2 Oracle tables and then use ELT components the way amaumont explained in his last post.

Thanks for your solution. Smiley Happy
You said:
? best way to do it is to load your 2 inputs in 2 Oracle tables? --> 1 STEP
and
?then use ELT components? --> 2 STEP
Then I have:
1. I used two tOracleInput and two tOracleOutput for write my selects into tables --> STEP 1
2. I used two tELTOracleInput and 1 tELTMap for the ?cross join? --> STEP 2
Result:
The result of the tELTOracleOutput is incorrect because STEP1 and STEP2 are non synchronized and they start together!!!
How can I resolve this problem? Smiley Sad
Employee

Re: Cartesian product

Ok, I see your problem.
The solution I propose is not the better solution because a bug prevents to connect correctly a third RunBefore from tOracleInput to tELTOracleMap. Even it is possible, it implies a compilation error. I created a bug report on this subject 1077.
So an alternative solution should be to use a tRunJob to start ELT processing like in the image.
One Star eng
One Star

Re: Cartesian product

Ok, I see your problem.
The solution I propose is not the better solution because a bug prevents to connect correctly a third RunBefore from tOracleInput to tELTOracleMap. Even it is possible, it implies a compilation error. I created a bug report on this subject 1077.
So an alternative solution should be to use a tRunJob to start ELT processing like in the image.

Thanks for your solution. It runs. Smiley Happy I hope the problem bug 1077 will be quickly resolved!
However the best solution should be to put a ?cross join? in the tMap component, because if I use ELT component, I must insert the result of tELTOracleMap into table before using it.
Why is the ?cross join? possible only in tELtOracleMap and not in the tMap?
Thanks a lot Smiley Happy Smiley Happy Smiley Happy
One Star eng
One Star

Re: Cartesian product

Ok, I see your problem.
The solution I propose is not the better solution because a bug prevents to connect correctly a third RunBefore from tOracleInput to tELTOracleMap. Even it is possible, it implies a compilation error. I created a bug report on this subject 1077.
So an alternative solution should be to use a tRunJob to start ELT processing like in the image.

Now, I can't use this solution because in the stap1 my job don't terminate with 2 tOracleOutput, but 1 tAggregateRow and 1 tMap (a)!!!!!
Amaumont do you have any solution? Smiley Sad
Employee

Re: Cartesian product

Can you post a snapshot of your job ?