This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes JDBC and the fly likes Problem with UNION query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem with UNION query" Watch "Problem with UNION query" New topic
Author

Problem with UNION query

Vaibhav Pingle
Greenhorn

Joined: Nov 23, 2006
Posts: 6
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

Joined: Mar 04, 2006
Posts: 98
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30352
    
150

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 ]

[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Vaibhav Pingle
Greenhorn

Joined: Nov 23, 2006
Posts: 6
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

Joined: Mar 04, 2006
Posts: 98
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_
 
wood burning stoves
 
subject: Problem with UNION query