Help coderanch get a
new server
by contributing to the fundraiser
  • 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Problem with UNION query

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 98
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
author & internet detective
Posts: 41936
911
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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_
 
please buy my thing and then I'll have more money:
We need your help - Coderanch server fundraiser
https://coderanch.com/t/782867/Coderanch-server-fundraiser
reply
    Bookmark Topic Watch Topic
  • New Topic