Results 1 to 10 of 10
  1. #1
    sashaxiv is offline Member
    Join Date
    Feb 2011
    Location
    Spain
    Posts
    26
    Rep Power
    0

    Default 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

  2. #2
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    12,050
    Rep Power
    20

    Default

    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.

  3. #3
    sashaxiv is offline Member
    Join Date
    Feb 2011
    Location
    Spain
    Posts
    26
    Rep Power
    0

    Default

    ok, this is my code:
    Java Code:
    		Statement statment = con.createStatement();
    		ResultSet resVentasCadenas = statment
    				.executeQuery(Querys.queryVentasTotal);
                                      ..........
                                      ..........
                          while (resVentasCadenas.next()) {
                                ....................
                           }

  4. #4
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    12,050
    Rep Power
    20

    Default

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

  5. #5
    sashaxiv is offline Member
    Join Date
    Feb 2011
    Location
    Spain
    Posts
    26
    Rep Power
    0

    Default

    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

  6. #6
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    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.

  7. #7
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    12,050
    Rep Power
    20

    Default

    Java Code:
    -- 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.

  8. #8
    sashaxiv is offline Member
    Join Date
    Feb 2011
    Location
    Spain
    Posts
    26
    Rep Power
    0

    Default

    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

  9. #9
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    12,050
    Rep Power
    20

    Default

    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?

  10. #10
    sashaxiv is offline Member
    Join Date
    Feb 2011
    Location
    Spain
    Posts
    26
    Rep Power
    0

    Default

    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.

Similar Threads

  1. JDBC Driver
    By Ursula in forum New To Java
    Replies: 6
    Last Post: 08-23-2010, 05:41 PM
  2. help for sqljdbc
    By cemo in forum Advanced Java
    Replies: 1
    Last Post: 05-12-2010, 03:21 AM
  3. union in linked list
    By IT student in forum New To Java
    Replies: 4
    Last Post: 01-02-2010, 11:34 AM
  4. Driver for Java-SQL
    By Pr0n in forum New To Java
    Replies: 7
    Last Post: 07-23-2009, 11:30 PM
  5. Printing the union and instersection of two sets
    By Java Tip in forum java.lang
    Replies: 0
    Last Post: 04-15-2008, 07:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •