I have a very simple join. two tables from Oracle. In M-Product Table, M_Product_ID is Primary Key. And this field is a foreign key in C_Invoiceline Table. Now I want to join them.
Select * from C_Invoiceline , M_Product where C_Invoiceline.M_Product_id = M_Product.M_Product_id;
I used the tMap. As in Image.
In my Job, M_Product is main and C_Invoiceline is Lookup, When M_Product have 775 rows and C_Invoiceline has 532 rows and all rows in C_Invoiceline have M_Product_Id, The result of this Join should have 532 rows. But when I run this job, as a result I have 572 rows. I don't know why??
I change the main and lookup and the result is 121 rows. I don't know why??
I want to have all matches. Then I select Inner Join and All matches in lookup table in tMap.
Only when I choose the C_Invoiceline as Main and M_Product as Lookup and I select the unique matches(But I want all matches), I will have 532 rows. Is it good?
NO
The content of Result is so bad. There are rows with same C_Invoiceline_Id. Some of are not real.
I executed query. And I'm sure that in result should be 532 rows with unique C_Invoiceline_Id.
Now please give me a complete image of a designed job for this query with all settings which you think is true, PLEASE.
Is there any one to help?
[img size=150]
Post edited by: Elham, at: 2008/03/12 08:19
Select * from C_Invoiceline , M_Product where C_Invoiceline.M_Product_id = M_Product.M_Product_id;
I used the tMap. As in Image.
In my Job, M_Product is main and C_Invoiceline is Lookup, When M_Product have 775 rows and C_Invoiceline has 532 rows and all rows in C_Invoiceline have M_Product_Id, The result of this Join should have 532 rows. But when I run this job, as a result I have 572 rows. I don't know why??
I change the main and lookup and the result is 121 rows. I don't know why??
I want to have all matches. Then I select Inner Join and All matches in lookup table in tMap.
Only when I choose the C_Invoiceline as Main and M_Product as Lookup and I select the unique matches(But I want all matches), I will have 532 rows. Is it good?
NO
The content of Result is so bad. There are rows with same C_Invoiceline_Id. Some of are not real.
I executed query. And I'm sure that in result should be 532 rows with unique C_Invoiceline_Id.
Now please give me a complete image of a designed job for this query with all settings which you think is true, PLEASE.
Is there any one to help?
[img size=150]
Post edited by: Elham, at: 2008/03/12 08:19
10 Answers:
Posted on March 12, 2008 at 10:39pm
Hi Elham,
From what you've described, you definitely want C_Invoiceline as the main table with M_Product as a Lookup. You should use an inner join. But if every row in C_Invoiceline has exactly 1 M_Product_id, then it shouldn't matter. Both an inner join and an outer join ought to return 532 rows.
It sounds like you have the ideas correct, but there must be some detail that is wrong.
Can you try to repost the image? It is not attached. Perhaps this will help us to see where the issue is.
Regards,
Matt
JasperSoft
From what you've described, you definitely want C_Invoiceline as the main table with M_Product as a Lookup. You should use an inner join. But if every row in C_Invoiceline has exactly 1 M_Product_id, then it shouldn't matter. Both an inner join and an outer join ought to return 532 rows.
It sounds like you have the ideas correct, but there must be some detail that is wrong.
Can you try to repost the image? It is not attached. Perhaps this will help us to see where the issue is.
Regards,
Matt
JasperSoft
Posted on April 2, 2008 at 11:27am
I tried to do this join with another component which is not clear for me.
I used the tELTOracleInput/Output/Map for this join. then the result is true. I mean in this way result of the join has 532 rows with right content.
Now I have 2 questions:
1- What is the difference between tELTOracleMap and tMap?
Just I know that tELTOracleMap work only with tELTOracleInput/Output components.
And one bad thing: I want to send the results of my join to a table which exist in another Database.
I mean another schema in Oracle (another connection). but tELTOracleOutput component don't let me do that. (For example, I want to read data from a table in Schema1 (a user or connection for Oracle)with a DBConnection and send the results to a table in Schema2 (another user or connection for Oracle)with another DBConnection. It doesn't work. I see both DBConnections in schema list, but I have error for it. I'm sure about the name of the table and I know that it's important that I use the exactly right name of my destination table for the link. It seems only the repository and schema of tELTOracleInput is important and we can not change the schema or even repository for output.
2- I'm sure that all details of the join are right. and with this new try (tELTOracleMap) and true results, I became more sure.
Then I don't know the problem with tMap.
size=400]
I used the tELTOracleInput/Output/Map for this join. then the result is true. I mean in this way result of the join has 532 rows with right content.
Now I have 2 questions:
1- What is the difference between tELTOracleMap and tMap?
Just I know that tELTOracleMap work only with tELTOracleInput/Output components.
And one bad thing: I want to send the results of my join to a table which exist in another Database.
I mean another schema in Oracle (another connection). but tELTOracleOutput component don't let me do that. (For example, I want to read data from a table in Schema1 (a user or connection for Oracle)with a DBConnection and send the results to a table in Schema2 (another user or connection for Oracle)with another DBConnection. It doesn't work. I see both DBConnections in schema list, but I have error for it. I'm sure about the name of the table and I know that it's important that I use the exactly right name of my destination table for the link. It seems only the repository and schema of tELTOracleInput is important and we can not change the schema or even repository for output.
2- I'm sure that all details of the join are right. and with this new try (tELTOracleMap) and true results, I became more sure.
Then I don't know the problem with tMap.
size=400]
Posted on April 2, 2008 at 11:29am
I tried to do this join with another component which is not clear for me.
I used the tELTOracleInput/Output/Map for this join. then the result is true. I mean in this way result of the join has 532 rows with right content.
Now I have 2 questions:
1- What is the difference between tELTOracleMap and tMap?
Just I know that tELTOracleMap work only with tELTOracleInput/Output components.
And one bad thing: I want to send the results of my join to a table which exist in another Database.
I mean another schema in Oracle (another connection). but tELTOracleOutput component don't let me do that. (For example, I want to read data from a table in Schema1 (a user or connection for Oracle)with a DBConnection and send the results to a table in Schema2 (another user or connection for Oracle)with another DBConnection. It doesn't work. I see both DBConnections in schema list, but I have error for it. I'm sure about the name of the table and I know that it's important that I use the exactly right name of my destination table for the link. It seems only the repository and schema of tELTOracleInput is important and we can not change the schema or even repository for output.
2- I'm sure that all details of the join are right. and with this new try (tELTOracleMap) and true results, I became more sure.
Then I don't know the problem with tMap.
size=400]
I used the tELTOracleInput/Output/Map for this join. then the result is true. I mean in this way result of the join has 532 rows with right content.
Now I have 2 questions:
1- What is the difference between tELTOracleMap and tMap?
Just I know that tELTOracleMap work only with tELTOracleInput/Output components.
And one bad thing: I want to send the results of my join to a table which exist in another Database.
I mean another schema in Oracle (another connection). but tELTOracleOutput component don't let me do that. (For example, I want to read data from a table in Schema1 (a user or connection for Oracle)with a DBConnection and send the results to a table in Schema2 (another user or connection for Oracle)with another DBConnection. It doesn't work. I see both DBConnections in schema list, but I have error for it. I'm sure about the name of the table and I know that it's important that I use the exactly right name of my destination table for the link. It seems only the repository and schema of tELTOracleInput is important and we can not change the schema or even repository for output.
2- I'm sure that all details of the join are right. and with this new try (tELTOracleMap) and true results, I became more sure.
Then I don't know the problem with tMap.
size=400]
Posted on April 9, 2008 at 12:13pm
I Find out what's the problem.
For every table in a DBConnection there is a schema. There is a schema for all columns of a table. When we connect to Oracle, and in the columns of DBType we have NUMBER, the type of data in jasper should be set to Integer. But it was float in my jasper. I changed float to integer. And now all tMap results are right.
It was a type mismatch. But it affects on results. Now all things are right.
Tanx
For every table in a DBConnection there is a schema. There is a schema for all columns of a table. When we connect to Oracle, and in the columns of DBType we have NUMBER, the type of data in jasper should be set to Integer. But it was float in my jasper. I changed float to integer. And now all tMap results are right.
It was a type mismatch. But it affects on results. Now all things are right.
Tanx
