JAVA...using a SQL Statement

[Michael Taylor]

I am having difficulty getting information off of a database with the following SQL Statement:
String strSQL ="SELECT d.Drugname,SUM(i.Received),SUM(i.Dispensed)
FROM Inventory i, Drug d GROUP BY i.InvDate, i.Drug_ID, d.Drugname HAVING i.Drug_ID=d.DrugID AND i.InvDate BETWEEN #"+startDate+"# AND #"+endDate+"#";
I am basically trying to get the DrugName from one table and the sum of two other columns from another table (joining by drugID) to populate a table. I believe I keep getting syntax errors. Can anyone help?
Thanks,
Michael

[Thomas Paul]

you need a group by for the two summed columns.

[Michael Taylor]

I thought the GROUP BY was for the columns that I wasn't using with the SUM function (or any of the other Totalling features). At least that is what my Oracle book says and Access queries write the SQL statement in a similar fashion to mine. Let me know if these are incorrect please. I have been fighting with this SQL statement for a while and I can't think of anything else.
I am not sure if it doesn't like the GROUP BY or the JOINing of the two tables.
Thanks,
Michael

[David Freels]

Try placing your simple join condition in the where clause. Also, GROUP BY must include all non-aggregate columns from both tables.
Hope this helps
David
[This message has been edited by David Freels (edited May 10, 2001).]

[Thomas Paul]

"SELECT d.Drugname, SUM(i.Received), SUM(i.Dispensed)
FROM Inventory i, Drug d
WHERE i.Drug_ID=d.DrugID
AND i.InvDate BETWEEN " + startDate + " AND " + endDate
" GROUP BY d.Drugname"
Also what form are startDate and endDate in? Oracle requires dates to be formatted as "1-JUL-2001".

[Michael Taylor]

When using GROUP BY I have to use the HAVING clause instead of the WHERE clause, so I am a little confused by the WHERE in the recent responses.
David, could you show me what you mean? I have my simple join after the HAVING clause and I am doing a GROUP BY on all non-aggregate columns that I am referencing. As far as the date is concerned...I am using Access and the date format is MM/DD/YY and that is what I am passing it. The reason I am passing the dates with #'s around them is because I was having trouble doing the BETWEEN on the InvDate column (formatted Date/Time) when the SQL statement was a little less than it currently is. When I added the #'s it fixed my initial problem, but didn't quite work all the time. Now that I have added the join I am not having any luck with the whole thing.
Sorry for the life story, but I was hoping to give a little more insight.
Thanks,
Michael

[Thomas Paul]

Originally posted by Michael Taylor:
When using GROUP BY I have to use the HAVING clause instead of the WHERE clause, so I am a little confused by the WHERE in the recent responses.

Who says? That is simply incorrect. The having is used to compare on the results of the grouping. The where is used to do single row comparisons. Imagine a table with 5 million rows and you want 50. Would it really make sense to do the grouping on the 5 million before you determine whether you even need the row?
One question... why are you using an Oracle book if you are using an Access database?

[Michael Taylor]

When I create the Query in Access and look at the SQL view of that query, I get something similar to what I have posted in my original message. The code is as follows:
SELECT Sum(Inventory.Received) AS SumOfReceived, Sum(Inventory.Dispensed) AS SumOfDispensed, Drug.DrugName
FROM Drug INNER JOIN Inventory ON Drug.DrugID = Inventory.Drug_ID
GROUP BY Inventory.InvDate, Inventory.Drug_ID, Drug.DrugName
HAVING (((Inventory.InvDate) Between [date1] And [date2]) AND ((Inventory.Drug_ID)=[drugID]));
The main difference is how the FROM part of the statement is put together. I even tried to put that in but I had a column not found error after I did that. I have even taken out my table aliases and typed the full names in from of each column name like above, but that made no difference. Putting ( )'s around each statement like above didn't help either. I referred to my Oracle book (intro to SQL) because Access generates extra code on complex queries that works in access only (according to Access instructors at school). Since I am writing an SQL statement I referred to my SQL book from Oracle for more guidance. Are you saying Java ignores the conventions of normal SQL syntax rules? Java isn't doing a great job...unfortunately...with the SQL produced by the database it is querying from either. Is there a Java SQL statement book you can give as a recommendation?
Thanks,
Michael

[Thomas Paul]

Java does not have its own SQL. It simply passes the statement to the database. What is the actual error you are getting?

[Michael Taylor]

Sorry, it looks like your original advice worked(at least I am not getting a SQL synatx error)...I hadn't been able to check it because the school re-imaged my laptop and I had to get a copy of Visual Cafe to re-load. I have a new dilemma now. I am trying to set my values for the Vector I am filling from this SQL statement, but it is saying "Column not found". It is having a problem with "Received" and "Dispensed" columns. I am assuming that SUM function used in the SQL statement somehow screwed up the column name on this. I tried setting column aliases in the SQL statement and that didn't work and neither did trying to use column index values. I am not sure how to reference the columns now as I loop through the ResultSet to fill the Vector. Any advice oh wise one.
Thanks,
Michael

[Michael Taylor]

Nevermind...I had tried the index value of the columns (0,1,2), but forgot that I needed to start with 1 and not 0. It looks like everything is working. I need to test and check some more, but for now I am O.K.
Thanks again,
Michael