部分代码:
ClientDataSet1.Open;
DCOMConnection1.Connected:= False;
ClientDataSet1.First;
while not ClientDataSet1.Eof do begin
ADO_YZ.Close;
ADO_YZ.SQL.Clear;
ADO_YZ.SQL.Add('insert HB_yz(BHID,YZLX,YZBM,DW,DJ,ZL,GG) ') ;
ADO_YZ.SQL.Add('SELECT ');
ADO_YZ.SQL.Add(' BHID='''+ADOQuery1.FieldByName('BHID').AsString+''', ');
ADO_YZ.SQL.Add(' YZLX='''+DATETOSTR( ClientDataSet1.FieldByName('YZLX').AsInteger)+''' , ');
......
ADO_YZ.ExecSQL;
ClientDataSet1.Next;//由于YZLX的值有多种方式体现,我想将结果集插入hb_yz表时像如下SQL样,请问如何写?
insert hb_yz (bhid,yzlx)
select bhid,yzlx=case when yzlx=0 then 1
when yzlx=3 then 3
when yzlx=4 then 2
whenyzlx in (1,2,5) then 0
end
from 表
ClientDataSet1.Open;
DCOMConnection1.Connected:= False;
ClientDataSet1.First;
while not ClientDataSet1.Eof do begin
ADO_YZ.Close;
ADO_YZ.SQL.Clear;
ADO_YZ.SQL.Add('insert HB_yz(BHID,YZLX,YZBM,DW,DJ,ZL,GG) ') ;
ADO_YZ.SQL.Add('SELECT ');
ADO_YZ.SQL.Add(' BHID='''+ADOQuery1.FieldByName('BHID').AsString+''', ');
ADO_YZ.SQL.Add(' YZLX='''+DATETOSTR( ClientDataSet1.FieldByName('YZLX').AsInteger)+''' , ');
......
ADO_YZ.ExecSQL;
ClientDataSet1.Next;//由于YZLX的值有多种方式体现,我想将结果集插入hb_yz表时像如下SQL样,请问如何写?
insert hb_yz (bhid,yzlx)
select bhid,yzlx=case when yzlx=0 then 1
when yzlx=3 then 3
when yzlx=4 then 2
whenyzlx in (1,2,5) then 0
end
from 表
select bhid,
case when yzlx=0 then 1
when yzlx=3 then 3
when yzlx=4 then 2
when yzlx in (1,2,5) then 0 end yzlx
from 表
select bhid,
case when yzlx=0 then 1
when yzlx=3 then 3
when yzlx=4 then 2
when yzlx in (1,2,5) then 0 end yzlx
from 表
在D中用动态SQL实现~
1.
case yzlx of
0:tmp:=1;
3:tmp:=3;
4:tmp:=4;
1,2,5:tmp:=0
end;
with query1 do
begin
...
...
sql.text:='insert into ... select bhid '+inttostr(tmp)+' from table1'
execsql;
end;