SQL Questions
So here I am starting the planning of a replacement web application for my company. We have grown exponentially and need to upgrade from our ASP & AccessDB setup. I am thinking of going to JSP/EJB/Servlets and Oracle. I know that this is a logical choice but while I am experimenting with my test code I have run into a problem.
I am running a query against my db and I need to take one value from the first query, and in ASP I would just put in a do loop
i.e.. <% do until rs.eof%>
I know that java will do loops better than VBScript any day of the week but here is where the twist comes in. On the old system I would execute a query:
rs = SELECT User_ID, UserName, Office_ID FROM Accounts WHERE Account_Status = 'Active'
Then a do loop
do until rs.eof
Then I would take data from the first query to build a second dynamic query:
rs1 = SELECT Region, Office_ID FROM Offices WHERE Office_ID = " & rs("Office_ID")
Then I would output to a table all the found relevant information
Name: <%=rs("UserName")%> output would be: John Smith
Office: <%=rs1("Office_ID")%>output would be: 234
Region: <%=rs1("Region")%> output would be: Texas
Then I would put (in ASP/VBScript)
rs.movenext
loop
And the system would get the next record from the first recordset (rs) and start the second query (rs1) again.
I am looking for a simple code snippet or explanation on how to do this in java.
Thanks in advance
[1545 byte] By [
cto1maca] at [2007-9-29 16:32:15]
If I get you right, it should look like this (given object "conn" is a database connection):
Statement accountStmt=conn.createStatement();
PreparedStatement userStmt=conn.prepareStatement("SELECT region,office_id FROM offices WHERE office_id=?");
ResultSet accountSet=accountStmt.executeQuery("SELECT user_id,username,office_id FROM accounts WHERE account_status='Active'");
ResultSet userSet;
///
while (accountSet.next()) {
userStmt.setInt(1,accountSet.getInt("office_id"));
userSet=userStmt.executeQuery();
if (userSet.next()) {
String name=userSet.getString("username");
String office=userSet.getString("office_id");
String region=userSet.getString("region");
/* do something with it */
}
userSet.close();
}
accountSet.close();
accountStmt.close();
userStmt.close();
Never forget to close result sets and statements when working with oracle, otherwise you can run into problems with too many open db cursors.
Regards
>
> rs = SELECT User_ID, UserName, Office_ID FROM Accounts
> WHERE Account_Status = 'Active'
>
>
> rs1 = SELECT Region, Office_ID FROM Offices WHERE
> Office_ID = " & rs("Office_ID")
>
What you should really do is a join on your tables. This will drastically reduce network traffic and Oracle will run this in a snap.
Doing a lookup for the region for every row in your first result set will be many times slower, as many times as there are rows.
sql = "SELECT user_id, username, region "
+ "FROM accounts, offices "
+ "WHERE accounts.office_id = offices.office_id";
ted is right, I didn't think over that. However, the query lacks one constraint from the first query and should read
sql = "SELECT user_id, username, region "+
"FROM accounts, offices "+
"WHERE accounts.office_id = offices.office_id AND "+
"accounts.account_status='Active'";
Regards
Oooops,Not good considering I've been an Oracle consultant for the last 5 and a bit years...
I thank ya'll for the information, the only reason that I cannot do a join is that this is only a sample. There are dozens of places in the site I am planning where I will have to do queries on information from previous queries. I am trying the first solution right now.
Well, you should be doing joins in all your sql. Way, way, way more efficient than your planned design.
If you have 1000 records and your getting the region for each one example. It'll probably take in the region of 500 times longer to do it your way. That might be a slight exageration (but not too much). It also depends on how your connecting to the database. If you're using a thin client then think of all that meta info going back every time. Each database hit means constructing the sql, sending it, database parsing the statement, fetch the data, bundle up the data with the meta data, send it all back for parsing by the app/servlet/etc.
The actual data retrieval is trivial. All the time and effort is in the parsing, marshalling and packaging of the data. With very few records this probably isnt noticeable but seeing as you "have grown exponentially" I'm guessing you have large quantities of data to keep your processors warm!
Just to let you know....
Rob.
Well the only problem is that say i do the first sql statement on a in-company memo that requires all regional managers to report information on.
I query one table for all records that have the report:
SELECT * FROM Memorandums WHERE Memo_ID = 17
Then I need a listing of all the people who have read and not read the memo.
So the only way that I have found to do this is to havea separate statement.
SELECT * FROM Managers WHERE IN ( SELECT Manager_ID FROM Memorandums WHERE Memo_ID = 17)
This query will tell me all my managers who have read but will not give me a listing of the managers who have not read the memo. So that under the existing VBScript/ASP I would do a search this way:
rs = SELECT * FROM Managers
do until rs.eof
rs1 = SELECT * FROM Memorandums WHERE Memo_ID = 17 AND Manager_ID = " & rs("ID")
//Then to check for people who have and have not read the memo
If rs1.eof then
Response.write rs("Manager_Name") & " Has not read this memo as of " & Date(Now)
Else
Response.write rs("Manager_Name") & " Has read this memo as of " & Date(Now)
End if
rs.movenext
loop
See what I mean I would love to just join the tables and do these pages with single sql statements but, this is not able to happen or my sql skills are very lacking (Quite possible). Thanks again.
SELECT * FROM Managers WHERE IN ( SELECT Manager_ID FROM Memorandums WHERE Memo_ID = 17)unionSELECT * FROM Managers WHERE NOT IN ( SELECT Manager_ID FROM Memorandums WHERE Memo_ID = 17)
> SELECT * FROM Managers WHERE IN ( SELECT Manager_ID
> FROM Memorandums WHERE Memo_ID = 17)
> union
> SELECT * FROM Managers WHERE NOT IN ( SELECT
> Manager_ID FROM Memorandums WHERE Memo_ID = 17)
>
uh?
What you want EverNewJoy ?
Looks like you need some dier help with you sql skills...
Theres not a hell's chance of that SQL working. 'WHERE IN'; you need a column inbetween 'WHERE' and 'IN' to map to Manager_ID in your inner select.
> > SELECT * FROM Managers WHERE IN ( SELECT Manager_ID
> > FROM Memorandums WHERE Memo_ID = 17)
> > union
> > SELECT * FROM Managers WHERE NOT IN ( SELECT
> > Manager_ID FROM Memorandums WHERE Memo_ID = 17)
> >
>
> uh?
>
> What you want EverNewJoy ?
>
> Looks like you need some dier help with you sql
> skills...
>
> Theres not a hell's chance of that SQL working.
> 'WHERE IN'; you need a column inbetween 'WHERE' and
> 'IN' to map to Manager_ID in your inner select.
I know that a col name is reqd b/w where & in. I just copied & pasted his sql & added not to the unioned sql. I just assumed (I guess that was my mistake) that he knew that the col name was missing from his posted sql and that others would realize that all I did was copy & paste.
BTW, this is not my 1st sql of this type. I've written enough of sqls in my life (for Oracle, Teradata, DB2, Sybase, Access) to know that it will not work as written. But glad that you pointed it out just in case the OP didn't know what he/she was talking about.
Sorry EverNewJoy, didnt read the OP's last post jsut yours, not quite sure how i managed that :-s
Though the OP is asking for a list of Managers who have read the memo, and a list that hasn't ( I think). Your SQL just gets a list of all the managers in a very bad, roundabout way.
>
> SELECT * FROM Memorandums WHERE Memo_ID = 17
>
> Then I need a listing of all the people who have read
> and not read the memo.
>
> So the only way that I have found to do this is to
> havea separate statement.
> SELECT * FROM Managers WHERE IN ( SELECT Manager_ID
> FROM Memorandums WHERE Memo_ID = 17)
For the list of managers who have read the memorandum...
SELECT *
FROM Managers, Memorandums
WHERE Managers.Manager_ID = Memorandums.Manager_ID
AND Memo_ID = 17
And for those that haven't
SELECT *
FROM Managers
MINUS
SELECT *
FROM Managers, Memorandums
WHERE Managers.Manager_ID = Memorandums.Manager_ID
AND Memo_ID = 17
Hope that helps.
Rob.
From the OP's post I thought he wanted a list of all managers with a way to identify who has & who hasn't read the report. I know unions have costs associated with them and in my union'ed sql I did not include a flag to identify which mgr had & which hadn't read the report; thougt to leave that part out to the OP.
In my many years, I've not used MINUS. Isn't it pretty much the same as "select ... where manager.id not in (select ...Memo_ID = 17)"? Is there a performance gain by using MINUS over the "not in" ver? Is it better optimized by the db? If so, by all DBs?
As someone had pointed out in one of the other posts: regardless of how many years of experience one has, one always learns something new just by reading these fora posts. I for one certainly have.
>
> As someone had pointed out in one of the other posts:
> regardless of how many years of experience one has,
> one always learns something new just by reading these
> fora posts. I for one certainly have.
I agree. Though admittedly this isnt a SQL forum.
As for using minus, it is more efficient than 'where not in' as the 'where not in' gets executed for every row when the check is made. Using minus causes the two selectes to be run then the latter is removed from the former. You should generally avoid those inner selects if you can, unless the inner table contains few rows.
To the OP....
If you want one list then want to process it you could use an outer join instead. Can't remember what the performance is like compared to 'UNION'.
SELECT Manager_ID, memo_id
FROM Managers, Memorandums
WHERE Managers.Manager_ID = Memorandums.Manager_ID (+)
AND Memo_ID = 17
When you go through the list you just check to see if memo_id is null, this means the manager has not read the memo.
Rob.
Rob, even if the OP wants a list of all managers with some identifier to know who has & who hasn't read the report, I see the elegance of your solution:
To the OP:
Something like the following will probably work for you.
SELECT col1, col2,... 'Y' as Read_Flag
FROM Managers, Memorandums
WHERE Managers.Manager_ID = Memorandums.Manager_ID AND Memo_ID = 17
union
(
SELECT col1, col2,... 'N' as Read_Flag
FROM Managers
MINUS
SELECT * FROM Managers, Memorandums
WHERE Managers.Manager_ID = Memorandums.Manager_ID AND Memo_ID = 17
)
WOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO
Are you mad?
So you want to do like 5 index scans?
Why?
If you're reading this OP, I havelost all faith in everjoy's ability to write SQL. Yes it would work but I doubt the OP wants to go and make a cup of tea everytime they run the report!!!!!!
ted_trippin, I defer to your superior sql skills (& this is honest, not sarcasm).OP, his outerjoin sql is probably the most optimal solution you will find.
There must be something about ASP and VB Script that encourages this kind of programming style. We've got some ASP/VB Script Web developers here, and they ALL do their joins in code rather than let the RDB engine do it for them.
Maybe it's indicative of a lack of background that the M$ "point & click, drag & drop, it's easy to be a programmer" mentality encourages.
Forget about table scans (bad enough, I'll admit). What about all the network round trips that this brain-dead design will cost? Network latency is going to kill it.
(sigh) No wonder all the jobs are going to smart Indian programmers. - MOD
No kidding. I've got some VB programs which I wrote a couple of years ago and they've been running fine ever since. One day (yesterday) I find myself two timezones away from the server where the data is located, and a program that should have taken 10 seconds to run over the LAN takes 10 minutes to run over the WAN. Fortunately these programs are the target of a project to improve their reliability, which means they are going to be rewritten in some other language and run on some other platform.
> ted_trippin, I defer to your superior sql skills (&
> this is honest, not sarcasm).
>
> OP, his outerjoin sql is probably the most optimal
> solution you will find.
Excuse any ranting from yesterday!!
Was a stressful day, apologies for any 'rubbing the wrong way' :-/
Rob.
