Win a copy of Learn Spring Security (video course) this week in the Spring forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Problem with UNION query

 
Vaibhav Pingle
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi frnds....

i would like to know if java supports a result set containng a query of type UNION i.e. a query containng a UNION of two SELECT statements

i am askng this becoz, i want to create a JTable using such a query.
The code which i have written to create a TableModel from the ResultSet has been created and is working very fine......for normal queries....but it does not work for the union query...

infact i tried to get get the output directly from the result set.....but it returns only the data that is returned from the 1st select statement in the UNION query. and the data return after the UNION keyword in the query is not displayed.....

I am using the MS Access as my database..and the same UNION query is running fine in the MS access query generator

please help me out.....if any one has come across such a situation and that has found any solution
 
Chris Hendy
Ranch Hand
Posts: 98
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This isn't an answer so much as a work around (aka a grievous hack).

Create a view in the database which returns the results of the UNION'd query, and reference the view from the Java Tier.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34071
331
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Vaibhav,
JDBC supports any query your database can run. Maybe the problem is in the MS Access driver?

It's also possible that there is a syntax error in the SQL statement. Can you post it here? In particular, I would look to see if there aren't extra semicolons in the statement.
[ November 25, 2006: Message edited by: Jeanne Boyarsky ]
 
Vaibhav Pingle
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well this is the query....which gives me error

SELECT Trades.stock AS Stock, Bills.bdate AS Date_, Trades.qty AS Quantity, Trades.buy_price AS Price
FROM Trades, Bills
WHERE (((Bills.bdate)<=Format('" + date + "', 'dd/MM/yyyy'))
And ((Trades.status)='buy')
And ((Trades.buy_bill)=Bills.bnumber))
ORDER BY Trades.stock, Bills.bdate
UNION
SELECT Trades.stock AS Stock, Bills.bdate AS Date_, (0-Trades.qty) AS Quantity, Trades.sell_price AS Price
FROM Trades, Bills
WHERE (((Bills.bdate)<=Format('" + date + "', 'dd/MM/yyyy'))
And ((Trades.status)='sell')
And ((Trades.sell_bill)=Bills.bnumber))
ORDER BY Trades.stock, Bills.bdate

please ignore the syntax of this part '" + date + "' as i have copied it from the java statement
 
Chris Hendy
Ranch Hand
Posts: 98
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What happens if you order the result of the UNION rather than the constituent queries.

SELECT Trades.stock AS Stock, Bills.bdate AS Date_, Trades.qty AS Quantity, Trades.buy_price AS Price
FROM Trades, Bills
WHERE (((Bills.bdate)<=Format('" + date + "', 'dd/MM/yyyy'))
And ((Trades.status)='buy')
And ((Trades.buy_bill)=Bills.bnumber))
UNION
SELECT Trades.stock AS Stock, Bills.bdate AS Date_, (0-Trades.qty) AS Quantity, Trades.sell_price AS Price
FROM Trades, Bills
WHERE (((Bills.bdate)<=Format('" + date + "', 'dd/MM/yyyy'))
And ((Trades.status)='sell')
And ((Trades.sell_bill)=Bills.bnumber))
ORDER BY Stock, Date_
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic