Win a copy of Learning Regular Expressions this week in the General Computing forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
  • Devaka Cooray
Sheriffs:
  • Jeanne Boyarsky
  • Knute Snortum
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Ganesh Patekar
  • Stephan van Hulst
  • Pete Letkeman
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Ron McLeod
  • Vijitha Kumara

JAVA...using a SQL Statement  RSS feed

 
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
mister krabs
Ranch Hand
Posts: 13974
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you need a group by for the two summed columns.
 
Michael Taylor
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
mister krabs
Ranch Hand
Posts: 13974
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"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
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
mister krabs
Ranch Hand
Posts: 13974
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
mister krabs
Ranch Hand
Posts: 13974
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!