Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Ryukai
Starting Member
2 Posts |
 Posted - 2012-05-10 : 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! |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-05-10 : 08:29:04
|
This forum is for MIcrosoft SQL Server, so any expertise in AS400 is likely far and few in between. You would get better and faster responses at a db2 forum or generalized forums such as dbforums.com.If it were SQL Server, I would try something like this:create table #tmp(name3 varchar(255) not null primary key clustered);insert into #tmp select distinct name3 from table3;SELECT * FROM table1 LEFT JOIN table2 ON ID1=ID2 LEFT JOIN #tmp on #tmp.name3 = table2.name2WHERE table1.name1 = ‘GRADE: A’AND ( table2.name2 = ‘GRADE: A’ OR #tmp.name3 is not null ) |
 |
|
|
Ryukai
Starting Member
2 Posts |
Posted - 2012-05-10 : 08:34:31
|
quote:
Originally posted by sunitabeck
This forum is for MIcrosoft SQL Server, so any expertise in AS400 is likely far and few in between. You would get better and faster responses at a db2 forum or generalized forums such as dbforums.com.If it were SQL Server, I would try something like this:create table #tmp(name3 varchar(255) not null primary key clustered);insert into #tmp select distinct name3 from table3;SELECT * FROM table1 LEFT JOIN table2 ON ID1=ID2 LEFT JOIN #tmp on #tmp.name3 = table2.name2WHERE table1.name1 = ‘GRADE: A’AND ( table2.name2 = ‘GRADE: A’ OR #tmp.name3 is not null )
Ups, sorry, thanks anyhow :) |
|
|
|
|
|
|
SQL Excel