sqljdbc driver UNION querys

[sashaxiv]

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

[Tolls]

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.

[sashaxiv]

ok, this is my code:

		Statement statment = con.createStatement();
		ResultSet resVentasCadenas = statment
				.executeQuery(Querys.queryVentasTotal);
                                  ..........
                                  ..........
                      while (resVentasCadenas.next()) {
                            ....................
                       }

[Tolls]

Which says nothing about your query.
What is the SQL?

[sashaxiv]

Sorry about the delay,
the sql query sintax is as folllows and it is working in the sqlservermanager:

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

[masijade]

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, self-contained, 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.

[Tolls]

-- ventas mismas tiendas

Don't stick comments in your SQL unless you can guarantee that they will appear on a single line by themselves.

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.

[sashaxiv]

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?

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

[Tolls]

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?

[sashaxiv]

Forget it,
now it was my mistake(i was giving the parameters in a wrong way to the statment and only the third part of the statment was retrieving results). With your solution the statment is working without problems.

Very much thanks for your time Toll.
Regards from Spain.