Results 1 to 10 of 10
  1. #1
    Join Date
    May 2012
    Posts
    4

    Unanswered: AS400 SQL with Excel

    Posted - 05/10/2012 : 08:05:50
    --------------------------------------------------------------------------------

    Hi,
    I’m working with Excel 2010 and I’m trying to extract from an AS400 database some tables in Excel with a query (with ODBC driver).
    The AS400 database is read only, so I can only do SELECT instructions. My code is:

    SELECT *
    FROM table1 LEFT JOIN table2 ON ID1=ID2
    WHERE table1.name1 = ‘GRADE: A’
    AND ( table2.name2 = ‘GRADE: A’ OR table2.name2 IN ( SELECT table3.name3 FROM table3 ) )

    Table1 and table2 have nearly 3 million records both (this is why I have inserted the WHERE clause, for speeding up the query and let it search only a few name ). Table3 have only twenty records.
    The user input one name (in this case ‘GRADE: A’) and the query search it in table1 and table2 and also search in table2 the names wich are in table3 also (because sometimes some name have different ID etc: long to explain )

    My problem is that with the above code the query execute the second SELECT ( “SELECT table3.name3 FROM table3” ) for all the record in table2, making the query very long.
    My question is: is possible to stock the result of the second SELECT ( “SELECT table3.name3 FROM table3” ) in one string-array so the second query will be executed only one time and not 3 million times? Something like this:

    SET @TEST = (SELECT table3.name3 FROM table3)
    SELECT *
    FROM table1 LEFT JOIN table2 ON ID1=ID2
    WHERE table1.name1 = ‘GRADE: A’
    AND ( table2.name2 = ‘GRADE: A’ OR table2.name2 IN @TEST )

    I have tried to execute the above code but he give me an error: “The database is read only and you can’t execute this query” or something like this.

    Help!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Can you obtain an EXPLAIN plan for the query?

    Andy

  3. #3
    Join Date
    May 2012
    Posts
    4
    Quote Originally Posted by ARWinner View Post
    Can you obtain an EXPLAIN plan for the query?

    Andy
    I don't know what do you mean, sorry

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I suggest you ask your DBA.

    Andy

  5. #5
    Join Date
    Apr 2012
    Posts
    7
    this one should be definitely better:
    SELECT *
    FROM table1
    LEFT JOIN table2 ON ID1=ID2
    WHERE (table1.name1 = ‘GRADE: A’
    AND table2.name2 = ‘GRADE: A’) OR (table1.name1 = ‘GRADE: A’ AND exists (select 1 from table3 where table2.name2 = table3.name3))

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Ryukai View Post
    Posted - 05/10/2012 : 08:05:50
    --------------------------------------------------------------------------------

    ...
    ...

    SELECT *
    FROM table1 LEFT JOIN table2 ON ID1=ID2
    WHERE table1.name1 = ‘GRADE: A’
    AND ( table2.name2 = ‘GRADE: A’ OR table2.name2 IN ( SELECT table3.name3 FROM table3 ) )

    ...
    ...
    If a row of table1 has no corresponding row of table2,
    ( table2.name2 = ‘GRADE: A’ OR table2.name2 IN (...) )
    is unknown.
    Then, the row of table1 is not included in the result.

    So, LEFT JOIN in the query should be replaced by INNER JOIN.

  7. #7
    Join Date
    May 2012
    Posts
    4
    Quote Originally Posted by k_S View Post
    this one should be definitely better:
    SELECT *
    FROM table1
    LEFT JOIN table2 ON ID1=ID2
    WHERE (table1.name1 = ‘GRADE: A’
    AND table2.name2 = ‘GRADE: A’) OR (table1.name1 = ‘GRADE: A’ AND exists (select 1 from table3 where table2.name2 = table3.name3))
    Trying this but it require same time of mine.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You really need to see the EXPLAIN plan. DB2 does a rewrite of the SQL each time and all of the variations you have tried might all end up as the same SQL that is actually executed. You need to understand the EXPLAIN plan to see what DB2 is doing.

    If you do not have the appropriate indexes on the tables, then no amount of rewriting the query will net you anything.

    Andy

  9. #9
    Join Date
    May 2012
    Posts
    4
    Quote Originally Posted by ARWinner View Post
    You really need to see the EXPLAIN plan. DB2 does a rewrite of the SQL each time and all of the variations you have tried might all end up as the same SQL that is actually executed. You need to understand the EXPLAIN plan to see what DB2 is doing.

    If you do not have the appropriate indexes on the tables, then no amount of rewriting the query will net you anything.

    Andy
    Ok, thanks for the reply.

    Do you know if there's some way to see the EXPLAIN plan directly from Excel? Or I need to download some other software ?

  10. #10
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    As previously advised in this thread, talk with your DBA database administrator person(s), if you have such people at your shop.

    You need to do this because they should have the skills and knowledge to help you, and that shouldl be much more efficient than posting here.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •