Hitachi Vantara Pentaho Community Forums
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: How to generate partial result using Join Rows (Cartesian product)

  1. #1

    Default How to generate partial result using Join Rows (Cartesian product)

    Hi expert

    Is there any component to generate the record based two tables? It is like to set the condiations for join-rows-(cartesian-product).
    For example
    Table1
    field : userId, projectId
    value: 1, a
    2, c

    Table2:
    field: projectId, priority
    value: a, c1
    b, c1
    c, c2
    d, c2
    e, c3

    The result:read the records in table1 one by one. Then search the record when the priority in table2 is sames as the priority in table1, then insert a new record with the userId in table1 and the projectId/priority in table2.
    field:userId, projectId, priority
    value: 1, a, c1
    1, b, c1
    2, c, c2
    2, d, c2



    Thanks.
    Maria

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Let me help out, while all the experts are unavailable.

    Table Input should be your first choice if the tables are in the same database:

    Code:
    // off the top of my hat, not verified
    SELECT t1.userid, t2.projectid, t3.priority, 
    FROM users t1
    INNER JOIN projects t3 ON t3.projectid = t1.projectid
    INNER JOIN projects t2 ON t2.priority = t3.priority
    No problem for Kettle, if sufficiently small rowsets are involved, but always keep an eye on the Sort steps.
    Attached Files Attached Files
    So long, and thanks for all the fish.

  3. #3

    Default

    Hi, marabu

    Thank you very much for your reply. I didn't make myself clear. What I expected:
    /*
    read each line one by one for the first table, for example, value is 1,a.
    Then read the second table.
    select projectId,priority from Table2 where priority=(select priority from Table2 where projectId='a');
    a,c1
    b,c1
    Then add the result userId,projectId, priority to another table.
    So, the result is 1,a,c1
    1,b,c1


    Then go to the next record for the table1 until the last line of table1.
    */

    Thanks.
    Maria

  4. #4
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by mariax View Post
    What I expected
    What else did I get you?
    So long, and thanks for all the fish.

  5. #5

    Default

    Hi, marabu

    I am really sorry for the intonation and mood in my orignal message. I just want to describe what the question is. I didn't mean to offend you.
    I really appriecated your effort and your reply on my question.
    Actually, when I got your response everytime, it helps me a lot to understand how to use kettle.
    Thanks again.
    Maria

  6. #6
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    There is nothing you must worry about.
    At least I didn't notice a thing.
    Was it the emoticon that made you uncomfortable?
    Maybe I picked the wrong one.

    All the best for your project.

    So long, and thanks for all the fish.

  7. #7

    Default

    Marabu

    Thanks for your reply.
    Currently I used the java code to finish the data converting like below.


    private static void convertData() throws Exception {

    dbconn = copyToDB.getConnection();
    if (dbconn!=null){
    System.out.println("connection is not null");
    // Statement stmt = dbconn.createStatement();
    Statement stmt2 = dbconn.createStatement();
    Statement stmt3 = dbconn.createStatement();

    PreparedStatement xt = dbconn.prepareStatement("select * from contrat_dataWIthIndustryNo");
    ResultSet rs1= xt.executeQuery();
    while(rs1.next()){

    int user_global_id = rs1.getInt("nameNo");
    int projectNo1 = rs1.getInt("projectNo");
    int industryNo = rs1.getInt("industryNo");

    System.out.println("handle the data for" + user_global_id);

    String sql2 = "select projectNo, priorityNo from project_data where industryNo=" + industryNo + ";";
    ResultSet rs2= stmt2.executeQuery(sql2);
    while(rs2.next()){
    int projectNo2 = rs2.getInt("projectNo");
    int priorityNo = rs2.getInt("priorityNo");
    double level;
    if (projectNo1==projectNo2)
    level=1;
    else level = 0.5 + priorityNo * 0.04;

    String sql3 = "insert into contract_data_temp2 values(" + user_global_id + ","
    + projectNo2 + "," + industryNo + "," + level + ");";

    stmt3.executeUpdate(sql3);
    }
    }
    System.out.println("the db operation is finished.");
    stmt3.close();
    stmt2.close();
    dbconn.close();
    } else {
    throw new Exception("the file is not found .");
    }
    }

    Is there any kettle component to finish the same function?
    Or is there any example to share to invoke the java class?


    Thanks.
    Maria

  8. #8
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    I can see what you did there: 40 nice lines of Java code - instead of one ugly SQL statement.

    Code:
    -- still untested
    INSERT INTO contract_data_temp2 
    SELECT cd."nameNo", pd."projectNo", cd."industryNo", 
    CASE WHEN cd."projectNo" = pd."projectNo" THEN 1.0 ELSE 0.5 + pd."priorityNo" * 0.04 END AS level 
    FROM "contrat_dataWIthIndustryNo" cd 
    INNER JOIN project_data pd ON cd."industryNo" = pd."industryNo"
    So long, and thanks for all the fish.

  9. #9

    Default

    Marabu

    Wow! What an efficent SQL statement! So wonderful and powerful!
    I had a test just now, the result is same as the output of java code. Thank you so much.
    At the same time, I need a deep study on SQL.

    Thanks again.
    Maria

  10. #10

    Default

    Marabu

    Can you help to explain the meaning of the SQL. I had a check on the inner join on. But I still can't understand how this statement implement the function that I need.

    BTW, Do you have any weblink/website/book on SQL to recommand for a freshman like me?
    Thanks.
    Maria

    Thanks.
    Maria

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.