Hello all !
Problem:
SQL QUERY in {} - doesnt' WORK (but sentence is correct ! Update table SET field = where field= AND field=)
I made it in a russian way..i made a crutch with double work.. (1st SQL - search , 2nd table - edit..) but it's NOT right !
Form Example:
unit Menulocal_change;
interface
uses
Math, ExtCtrls,DB, Classes,SysUtils, MemDS, Uni, VirtualTable, CRBatchMove,VirtualQuery,StdCtrls, memData,VirtualDataSet,Dialogs;
procedure CALC_consumants(var mytable:TVirtualTable;var myconsumats:TVirtualTable);
implementation
procedure CALC_consumants(var mytable:TVirtualTable;var myconsumats:TVirtualTable);
var
Q_consumants,Q_sum:TVirtualQuery;
procent:int64;
begin
Q_consumants:=TVirtualQuery.Create(nil);
Q_consumants.SourceDataSets.Clear;
Q_consumants.SourceDataSets.Add(mytable,'','vt_menu');
Q_consumants.SourceDataSets.Add(myconsumats,'','vt_cons');
Q_sum:=TVirtualQuery.Create(nil);
Q_sum.SourceDataSets.Clear;
Q_sum.SourceDataSets.Add(mytable,'','vt_menu');
Q_sum.SourceDataSets.Add(myconsumats,'','vt_cons');
{$IFDEF DEBUG}
mytable.SaveToXML('mytable_BEFORE.xml');
{$ENDIF}
Q_consumants.SQL.Text:='SELECT * FROM vt_menu WHERE dopinfo_type=1';
Q_consumants.Open;
while not Q_consumants.Eof do
begin
{
Q_sum.SQL.Text:='UPDATE vt_menu SET dopinfo_price=('+
' (SELECT price FROM vt_menu WHERE unum='+Q_consumants.FieldByName('dopinfo_unum').AsString+')/'+
'(SELECT COUNT(*) FROM vt_menu WHERE dopinfo_type=1 AND dopinfo_unum='+Q_consumants.FieldByName('dopinfo_unum').AsString+')'+
'*(SELECT procent FROM vt_cons WHERE id = '+Q_consumants.FieldByName('dopinfo_id').AsString+')/100 '+
') WHERE dopinfo_id='+Q_consumants.FieldByName('dopinfo_id').AsString+' AND unum='+Q_consumants.FieldByName('unum').AsString;
Q_sum.ExecSQL;
}
Q_sum.SQL.Text:='SELECT ((SELECT price FROM vt_menu WHERE unum='+Q_consumants.FieldByName('dopinfo_unum').AsString+')/'+
'(SELECT COUNT(*) FROM vt_menu WHERE dopinfo_type=1 AND dopinfo_unum='+Q_consumants.FieldByName('dopinfo_unum').AsString+')'+
'*(SELECT procent FROM vt_cons WHERE id = '+Q_consumants.FieldByName('dopinfo_id').AsString+')/100 '+
') as dopinfo_price';
Q_sum.open;
Q_consumants.Edit;
Q_consumants.FieldByName('dopinfo_price').AsFloat:=Q_sum.FieldByName('dopinfo_price').AsFloat;
Q_consumants.Post;
Q_consumants.Next;
end;
{$IFDEF DEBUG}
mytable.SaveToXML('mytable_AFTER.xml');
{$ENDIF}
Q_consumants.Free;
Q_sum.Free;
end;
end.
8.3.2 TVirualQuery + TvirtualTable = BUG...
Re: 8.3.2 TVirualQuery + TvirtualTable = BUG...
Thank you for the information. To investigate this behavior of VirtualDAC, please compose a full sample demonstrating the issue and send it to us, including database objects creating scripts.
You can send the sample using the contact form at our site: devart.com/company/contactform.html
You can send the sample using the contact form at our site: devart.com/company/contactform.html