Results 1 to 10 of 10
Thread: sqljdbc driver UNION querys
 02182011, 01:13 PM #1Member
 Join Date
 Feb 2011
 Location
 Spain
 Posts
 26
 Rep Power
 0
sqljdbc driver UNION querys
Hi all,
i am having a problem with the sqljdbc driver related with Union querys.
My sql query is in the form QUERY1 Union QUERY2 Union Query3.
At the sqlServerManager i can get the data of the 3 querys, but at my application i only get data of the first query.
Can anyone tell me if this is related with my driver?
thanks in advance
 02182011, 03:13 PM #2Moderator
 Join Date
 Apr 2009
 Posts
 12,933
 Rep Power
 22
Doubt it.
The driver simply passes the SQL to the database.
If you showed us your code we might be able to point out the problem.
 02182011, 03:44 PM #3Member
 Join Date
 Feb 2011
 Location
 Spain
 Posts
 26
 Rep Power
 0
ok, this is my code:
Java Code:Statement statment = con.createStatement(); ResultSet resVentasCadenas = statment .executeQuery(Querys.queryVentasTotal); .......... .......... while (resVentasCadenas.next()) { .................... }
 02182011, 04:57 PM #4Moderator
 Join Date
 Apr 2009
 Posts
 12,933
 Rep Power
 22
Which says nothing about your query.
What is the SQL?
 02242011, 09:42 AM #5Member
 Join Date
 Feb 2011
 Location
 Spain
 Posts
 26
 Rep Power
 0
Sorry about the delay,
the sql query sintax is as folllows and it is working in the sqlservermanager:
Java Code:select sum(v.importeConIva * p.cambioreferencia) as importeTicket, t.cadena, v.seccion, 'TTTT', p.pais, p.nombre from XXXXXX v, XXXXXX t, XXXXXX paises p where v.tienda = t.tienda and t.pais = p.pais and v.fecha = CONVERT(DATETIME, '02/14/2011') and t.cerrada = 0 and t.cadena = 1 and v.seccion in (1,2) group by t.cadena, p.pais, p.nombre ,v.seccion  ventas mismas tiendas UNION ALL select sum(v.importeConIva * p.cambioreferencia) as importeTicket, t.cadena, v.seccion, 'MMTT', p.pais, p.nombre from XXXXXX v, XXXXXX t, XXXXXX paises p where v.tienda = t.tienda and t.pais = p.pais and t.cadena = 1 and v.seccion in (1,2) and v.fecha = CONVERT(DATETIME, '02/14/2011') and 0 < (select count (*) from XXXXX v2 where v2.tienda = v.tienda and v2.fecha = CONVERT(DATETIME, '02/15/2010') and t.cerrada = 0 ) group by t.cadena, p.pais, p.nombre, v.seccion UNION ALL select sum(v.importeConIva * p.cambioreferencia) as importeTicket, t.cadena, v.seccion, 'MMTT_A1' , p.pais, p.nombre from XXXXXX v, XXXXXX t, XXXXXX paises p where v.tienda = t.tienda and t.pais = p.pais and v.fecha = CONVERT(DATETIME, '02/15/2010') and t.cerrada = 0 and t.cadena = 1 and v.seccion in (1,2) group by t.cadena, p.pais, p.nombre, v.seccion order by t.cadena, p.pais
 02242011, 09:50 AM #6Senior Member
 Join Date
 Jun 2008
 Posts
 2,571
 Rep Power
 11
Then you should get all of the information (in a single resultset of course). If you can prove that you do not, then create a small, selfcontained, compilable, executable program and the explicit data to go with it, that demonstrates the problem and submit that as a bug to the vendor from which you got the JDBC driver.
 02242011, 10:22 AM #7Moderator
 Join Date
 Apr 2009
 Posts
 12,933
 Rep Power
 22
Java Code: ventas mismas tiendas
Since I suspect you are concatenating that String together then the above comment is actually commenting out all the remaining bit of the SQL, since it's all on one line as far as the database is concerned.
ETA: And in case anyone is wondering, yes I have done this. In Hibernate, on a large stored query that I wanted to try and document. Funnily enough the query didn't work since 90% of it was actually commented out.
 02242011, 01:19 PM #8Member
 Join Date
 Feb 2011
 Location
 Spain
 Posts
 26
 Rep Power
 0
Ok, thank you Tolls, i have changed what you said about comments.
Now my sql code is as follows, but now i am only getting data of the 3 sql union query. I have no results about the 2 first querys.
¿what's happening now?
Java Code:select sum(v.importeConIva * p.cambioreferencia) as importeTicket, t.cadena, v.seccion, 'TTTT', p.pais, p.nombre " + " from XXXX v, TTTTT t, PPPPPP p " + " where v.tienda = t.tienda " + " and t.pais = p.pais " + " and v.fecha = CONVERT(DATETIME, ?) " + " and t.cerrada = 0 and t.cadena = ? and v.seccion in (1,2) and p.pais = ? " + " group by t.cadena, p.pais, p.nombre ,v.seccion " + " UNION ALL" + " select sum(v.importeConIva * p.cambioreferencia) as importeTicket, t.cadena, v.seccion, 'MMTT', p.pais, p.nombre " + " from VVVVVVV v, TTTTTTTT t, PPPPPPP p " + " where v.tienda = t.tienda " + " and t.pais = p.pais and t.cadena = ? and v.seccion in (1,2) and p.pais = ? " + " and v.fecha = CONVERT(DATETIME, ?) " + " and 0 < (select count (*) " + " from VVVV v2 " + " where v2.tienda = v.tienda " + " and v2.fecha = CONVERT(DATETIME, ?)" + " and t.cerrada = 0 )" + " group by t.cadena, p.pais, p.nombre, v.seccion" + " UNION ALL" + " select sum(v.importeConIva * p.cambioreferencia) as importeTicket, t.cadena, v.seccion, 'MMTT_A1' , p.pais, p.nombre " + " from VVVVVVV v, TTTTTTTT t, PPPPPPPP p " + " where v.tienda = t.tienda " + " and t.pais = p.pais " + " and v.fecha = CONVERT(DATETIME, ?) " + " and t.cerrada = 0 and t.cadena = ? and v.seccion in (1,2) and p.pais = ? " + " group by t.cadena, p.pais, p.nombre, v.seccion
 02242011, 01:28 PM #9Moderator
 Join Date
 Apr 2009
 Posts
 12,933
 Rep Power
 22
You've lost me there.
There are 3 parts to your query, which are UNIONed together.
You say you are getting data from these, so what are you having a problem with?
 02242011, 01:37 PM #10Member
 Join Date
 Feb 2011
 Location
 Spain
 Posts
 26
 Rep Power
 0
Similar Threads

JDBC Driver
By Ursula in forum New To JavaReplies: 6Last Post: 08232010, 05:41 PM 
help for sqljdbc
By cemo in forum Advanced JavaReplies: 1Last Post: 05122010, 03:21 AM 
union in linked list
By IT student in forum New To JavaReplies: 4Last Post: 01022010, 12:34 PM 
Driver for JavaSQL
By Pr0n in forum New To JavaReplies: 7Last Post: 07232009, 11:30 PM 
Printing the union and instersection of two sets
By Java Tip in forum java.langReplies: 0Last Post: 04152008, 07:35 PM
Bookmarks