CODEWITHSUNDEEP
delphisql-server-2005delphi-2010
Edgar Holguin
Edgar Holguin

Reputation: 187

SQL query doesnt finish in delphi

Im using SQL Server 2005 with Delphi 2010

I have a table ventas with id_venta (PK)= id_sale, total(of the sale), (more values), estado=state(active/inactive) and cumulative.

The table registers all the sales of articles of a store, and i need to register the cumulative sum of the total row by row. Im using the state field to specify the sales made in the shift if there were more than one shift in the day. Im using this query:

Declare @id integer; 
set @id=(Select min(id_venta) from ventas where estado='activo');

while(select @id)<=(Select max(id_venta) from ventas) 
  begin 
    update ventas set acumulado=(select sum(total) from ventas 
    where id_venta<=@id and estado='activo') where id_venta=@id
    select @id=@id+1 
  end

This query does exacly what i want when i run it on SQL but when i do it on delphi it only gives me the comulative of about 151 rows of 431, not finishing.

This is my delphi code:

conect.Q_equivalencias.Active:=false;
conect.Q_equivalencias.SQL.Clear;
conect.Q_equivalencias.SQL.Add('Declare @id integer; set @id=(Select min(id_venta) from ventas where estado='+char(39)+'activo'+char(39)+' );');
conect.Q_equivalencias.SQL.Add('while(select @id)<=(Select max(id_venta) from ventas) begin');
conect.Q_equivalencias.SQL.Add('update ventas set acumulado=(select sum(total) from ventas  ');
conect.Q_equivalencias.SQL.Add('where id_venta<=@id and estado='+char(39)+'activo'+char(39)+') where id_venta=@id');
conect.Q_equivalencias.SQL.Add('select @id=@id+1 end');
conect.Q_equivalencias.ExecSQL;

What do i have to do so my query in Delphi finishes?

Edit:

Strange thing just happened, i tried with conect.Q_equivalencias.Open; insted of conect.Q_equivalencias.ExecSQL; and ofcourse it throw me an error creating cursor handler but it finished the query, all rows were updated, why is that?

Tried many things and it seems that my query on delphi only updates 152 rows...

Upvotes: 1

Views: 671

Answers (1)

Lucero
Lucero

Reputation: 60190

Maybe adding a SET NOCOUNT ON; at the beginning of your query may solve the issue.

That said, I'd use a single UPDATE statement (or even better a VIEW or computed column) instead of such an odd query. I'm not sure if the query actually does what you really want it to do, but the following single statement should be doing the equivalent:

UPDATE ventas
    SET acumulado=(
        SELECT SUM(v.total)
        FROM ventas v
        WHERE v.id_venta<=ventas.id_venta AND estado='activo'
    ) WHERE id_venta>=(
        SELECT MIN(id_venta)
        FROM ventas
        WHERE estado='activo'
    );

Upvotes: 2

Related Questions