topic Re: Cartesian product in Installing and Upgrading

Cartesian product

Anonymous — Sat, 16 Nov 2024 14:36:45 GMT

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

Re: Cartesian product

Anonymous — Fri, 04 May 2007 13:27:48 GMT

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

Re: Cartesian product

Anonymous — Fri, 04 May 2007 16:19:18 GMT

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

Re: Cartesian product

Anonymous — Sat, 05 May 2007 08:09:34 GMT

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.

Re: Cartesian product

Anonymous — Mon, 07 May 2007 10:14:28 GMT

Hi, and sorry for my english
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

Re: Cartesian product

amaumont — Mon, 07 May 2007 12:53:54 GMT

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.

Re: Cartesian product

Anonymous — Mon, 07 May 2007 13:26:57 GMT

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

Re: Cartesian product

amaumont — Mon, 07 May 2007 13:56:15 GMT

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.

Re: Cartesian product

Anonymous — Wed, 09 May 2007 10:50:38 GMT

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?
but in tELTOracleMap I can?t write my select;

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?
but I don?t want to write a big cartesian product in 1 tOracleRow or 1 tOracleInput .
Thanks a lot

Re: Cartesian product

amaumont — Wed, 09 May 2007 12:20:35 GMT

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.

Re: Cartesian product

Anonymous — Wed, 09 May 2007 12:34:32 GMT

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

Re: Cartesian product

Anonymous — Wed, 09 May 2007 17:51:15 GMT

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?

Re: Cartesian product

amaumont — Thu, 10 May 2007 13:05:21 GMT

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

Re: Cartesian product

Anonymous — Thu, 10 May 2007 14:03:19 GMT

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

Re: Cartesian product

Anonymous — Thu, 10 May 2007 14:31:53 GMT

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.

Re: Cartesian product

Anonymous — Thu, 10 May 2007 14:38:16 GMT

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 I want

Re: Cartesian product

Anonymous — Thu, 10 May 2007 14:47:16 GMT

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.

Re: Cartesian product

Anonymous — Thu, 10 May 2007 17:19:32 GMT

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.
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?

Re: Cartesian product

amaumont — Fri, 11 May 2007 00:08:44 GMT

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.

Re: Cartesian product

Anonymous — Fri, 11 May 2007 16:59:22 GMT

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. 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