我想在delphi里用表变量,但是不知道怎么用,请各位大侠帮忙,下面是我的代码
with qryDataStat do
begin
Close;
SQL.Clear;
sSQL := Format( +' declare @TmpTxnDataStat table '
+' ( ' \\这里就过不去了
+' [id] int IDENTITY(1,1), '
+' [CurrencyID] char(3), '
+' [SysAccountNo] varchar(13), '
+' [SysBankName] nvarchar(64), '
+' [IoFlag] char(1), '
+' [FeeAmount] numeric(9), '
+' [InSumCount] int, '
+' [Ingetcnt] int, '
+' [OutSumCount] int, '
+' [Outgetcnt] int, '
+' [AllCount] int '
+' ) '
+' INSERT INTO @TmpTxnDataStat '
+' ( '
+' CurrencyID,SysAccountNo,SysBankName,IoFlag,FeeAmount,InSumCount,Ingetcnt,OutSumCount,Outgetcnt,AllCount '
+' ) '
+' select a.CurrencyID,c.SysAccountNo,c.SysBankName,a.Ioflag, '
+' (select FeeAmount from ProcFee where IOFlag=a.ioflag and TradeCodeID=a.TradeCodeID) as feeamount, '
+' sum(case when ioflag=''I'' then sumcount else 0 end ) as InSumCount, '
+' sum(case when ioflag=''I'' and TradeCodeID in (''%s'') then sumcount else 0 end ) as Ingetcnt, '
+' sum(case when ioflag=''O'' then sumcount else 0 end ) as OutSumCount, '
+' sum(case when ioflag=''O'' and TradeCodeID in (''%s'') then sumcount else 0 end ) as Outgetcnt, '
+' sum(case when ioflag=''I'' then sumcount else 0 end ) + sum(case when ioflag=''O'' then sumcount else 0 end ) as AllCount '
+' from TxnDataStat a, Bank b, SysAccount c '
+' where a.CurrencyID = b.CurrencyID '
+' and a.BankID = b.BankID '
+' and b.CurrencyID *= c.CurrencyID '
+' and b.SysAccountNo *= c.SysAccountNo '
+' and a.sysdate between ''%s'' and ''%s'' '
+' and a.CurrencyID = ''%s'' '
+' and b.SysAccountNo like ''%s'' '
+' and b.AreaNo like ''%s'' '
+' group by c.SysAccountNo,c.SysBankName,a.CurrencyID,a.Ioflag,a.TradeCodeID '
+' order by c.SysAccountNo,c.SysBankName '
+' SELECT CurrencyID,SysAccountNo,SysBankName, '
+' sum(InSumCount) as InSumCount,sum(Ingetcnt) as Ingetcnt, '
+' sum(case when IOFlag=''I'' then FeeAmount*Ingetcnt else 0 end ) as Inamount, '
+' sum(OutSumCount) as OutSumCount,sum(Outgetcnt) as Outgetcnt, '
+' sum(case when IOFlag=''O'' then FeeAmount*Outgetcnt else 0 end ) as Outamount, '
+' sum(AllCount) as AllCount '
+' FROM @TmpTxnDataStat '
+' group by SysAccountNo,SysBankName,CurrencyID '
+' order by SysAccountNo ',
[sSQL1,sSQL2,sSQL1,sSQL2,sStartDate,sEndDate,sCurrencyID,sSysAccountNo,sAreaNo]);
SQL.Add(sSQL);
Open;
with qryDataStat do
begin
Close;
SQL.Clear;
sSQL := Format( +' declare @TmpTxnDataStat table '
+' ( ' \\这里就过不去了
+' [id] int IDENTITY(1,1), '
+' [CurrencyID] char(3), '
+' [SysAccountNo] varchar(13), '
+' [SysBankName] nvarchar(64), '
+' [IoFlag] char(1), '
+' [FeeAmount] numeric(9), '
+' [InSumCount] int, '
+' [Ingetcnt] int, '
+' [OutSumCount] int, '
+' [Outgetcnt] int, '
+' [AllCount] int '
+' ) '
+' INSERT INTO @TmpTxnDataStat '
+' ( '
+' CurrencyID,SysAccountNo,SysBankName,IoFlag,FeeAmount,InSumCount,Ingetcnt,OutSumCount,Outgetcnt,AllCount '
+' ) '
+' select a.CurrencyID,c.SysAccountNo,c.SysBankName,a.Ioflag, '
+' (select FeeAmount from ProcFee where IOFlag=a.ioflag and TradeCodeID=a.TradeCodeID) as feeamount, '
+' sum(case when ioflag=''I'' then sumcount else 0 end ) as InSumCount, '
+' sum(case when ioflag=''I'' and TradeCodeID in (''%s'') then sumcount else 0 end ) as Ingetcnt, '
+' sum(case when ioflag=''O'' then sumcount else 0 end ) as OutSumCount, '
+' sum(case when ioflag=''O'' and TradeCodeID in (''%s'') then sumcount else 0 end ) as Outgetcnt, '
+' sum(case when ioflag=''I'' then sumcount else 0 end ) + sum(case when ioflag=''O'' then sumcount else 0 end ) as AllCount '
+' from TxnDataStat a, Bank b, SysAccount c '
+' where a.CurrencyID = b.CurrencyID '
+' and a.BankID = b.BankID '
+' and b.CurrencyID *= c.CurrencyID '
+' and b.SysAccountNo *= c.SysAccountNo '
+' and a.sysdate between ''%s'' and ''%s'' '
+' and a.CurrencyID = ''%s'' '
+' and b.SysAccountNo like ''%s'' '
+' and b.AreaNo like ''%s'' '
+' group by c.SysAccountNo,c.SysBankName,a.CurrencyID,a.Ioflag,a.TradeCodeID '
+' order by c.SysAccountNo,c.SysBankName '
+' SELECT CurrencyID,SysAccountNo,SysBankName, '
+' sum(InSumCount) as InSumCount,sum(Ingetcnt) as Ingetcnt, '
+' sum(case when IOFlag=''I'' then FeeAmount*Ingetcnt else 0 end ) as Inamount, '
+' sum(OutSumCount) as OutSumCount,sum(Outgetcnt) as Outgetcnt, '
+' sum(case when IOFlag=''O'' then FeeAmount*Outgetcnt else 0 end ) as Outamount, '
+' sum(AllCount) as AllCount '
+' FROM @TmpTxnDataStat '
+' group by SysAccountNo,SysBankName,CurrencyID '
+' order by SysAccountNo ',
[sSQL1,sSQL2,sSQL1,sSQL2,sStartDate,sEndDate,sCurrencyID,sSysAccountNo,sAreaNo]);
SQL.Add(sSQL);
Open;
解决方案 »
- 偷笑,散分
- 怎么在Cxgrid单元格内调字典里的数据?
- BetterADO使用过的请进
- 请问:子窗体的相对位置
- 请帮个忙!
- Delphi中有没有和C++中的new和delete一样的函数?
- ****怎么用ado访问oracle数据库,具体怎么操作???***
- 注册表问题;有一二进制键值,在注册表编辑器里看到是’D2 07 0C 00 00 00 0D 00’,如何取得这串字符?
- 请问TEdit控件从右边开始显示text内容是怎样实现的?
- 如何启动Windows 98自带的文件查找功能(即开始菜单中的查找)
- 怎么将字符串(string)内容复制到byte的数组中
- 关于数据保存,不知道是不是属于并发的?!
...
vSQL:= ' declare @TmpTxnDataStat table '
+' ( ' \\这里就过不去了
+' [id] int IDENTITY(1,1), '
+' [CurrencyID] char(3), '
+' [SysAccountNo] varchar(13), '
+' [SysBankName] nvarchar(64), '
+' [IoFlag] char(1), '
+' [FeeAmount] numeric(9), '
+' [InSumCount] int, '
+' [Ingetcnt] int, '
+' [OutSumCount] int, '
+' [Outgetcnt] int, '
+' [AllCount] int '
+' ) '
+' INSERT INTO @TmpTxnDataStat '
+' ( '
+' CurrencyID,SysAccountNo,SysBankName,IoFlag,FeeAmount,InSumCount,Ingetcnt,OutSumCount,Outgetcnt,AllCount '
+' ) '
+' select a.CurrencyID,c.SysAccountNo,c.SysBankName,a.Ioflag, '
+' (select FeeAmount from ProcFee where IOFlag=a.ioflag and TradeCodeID=a.TradeCodeID) as feeamount, '
+' sum(case when ioflag=''I'' then sumcount else 0 end ) as InSumCount, '
+' sum(case when ioflag=''I'' and TradeCodeID in (%s) then sumcount else 0 end ) as Ingetcnt, '
+' sum(case when ioflag=''O'' then sumcount else 0 end ) as OutSumCount, '
+' sum(case when ioflag=''O'' and TradeCodeID in (%s) then sumcount else 0 end ) as Outgetcnt, '
+' sum(case when ioflag=''I'' then sumcount else 0 end ) + sum(case when ioflag=''O'' then sumcount else 0 end ) as AllCount '
+' from TxnDataStat a, Bank b, SysAccount c '
+' where a.CurrencyID = b.CurrencyID '
+' and a.BankID = b.BankID '
+' and b.CurrencyID *= c.CurrencyID '
+' and b.SysAccountNo *= c.SysAccountNo '
+' and a.sysdate between %s and %s '
+' and a.CurrencyID = %s '
+' and b.SysAccountNo like %s '
+' and b.AreaNo like %s '
+' group by c.SysAccountNo,c.SysBankName,a.CurrencyID,a.Ioflag,a.TradeCodeID '
+' order by c.SysAccountNo,c.SysBankName '
+' SELECT CurrencyID,SysAccountNo,SysBankName, '
+' sum(InSumCount) as InSumCount,sum(Ingetcnt) as Ingetcnt, '
+' sum(case when IOFlag=''I'' then FeeAmount*Ingetcnt else 0 end ) as Inamount, '
+' sum(OutSumCount) as OutSumCount,sum(Outgetcnt) as Outgetcnt, '
+' sum(case when IOFlag=''O'' then FeeAmount*Outgetcnt else 0 end ) as Outamount, '
+' sum(AllCount) as AllCount '
+' FROM @TmpTxnDataStat '
+' group by SysAccountNo,SysBankName,CurrencyID '
+' order by SysAccountNo ');
sSQL := format(sSQL, [sSQL1,sSQL2,sSQL1,sSQL2,sStartDate,sEndDate,sCurrencyID,sSysAccountNo,sAreaNo]);
with qryDataStat do
begin
Close;
SQL.Clear;
SQL.Text := sSQL;
Open;
end;
...
ssql,sSQL1,sSQL2,sStartDate,sEndDate,sCurrencyID,sSysAccountNo,sAreaNo: String;
begin
with qryDataStat do
begin
Close;
SQL.Clear;
sSQL := Format( ' declare @TmpTxnDataStat table '
+' ( '//这里就过不去了
+' [id] int IDENTITY(1,1), '
+' [CurrencyID] char(3), '
+' [SysAccountNo] varchar(13), '
+' [SysBankName] nvarchar(64), '
+' [IoFlag] char(1), '
+' [FeeAmount] numeric(9), '
+' [InSumCount] int, '
+' [Ingetcnt] int, '
+' [OutSumCount] int, '
+' [Outgetcnt] int, '
+' [AllCount] int '
+' ) '
+' INSERT INTO @TmpTxnDataStat '
+' ( '
+' CurrencyID,SysAccountNo,SysBankName,IoFlag,FeeAmount,InSumCount,Ingetcnt,OutSumCount,Outgetcnt,AllCount '
+' ) '
+' select a.CurrencyID,c.SysAccountNo,c.SysBankName,a.Ioflag, '
+' (select FeeAmount from ProcFee where IOFlag=a.ioflag and TradeCodeID=a.TradeCodeID) as feeamount, '
+' sum(case when ioflag=''I'' then sumcount else 0 end ) as InSumCount, '
+' sum(case when ioflag=''I'' and TradeCodeID in (''%s'') then sumcount else 0 end ) as Ingetcnt, '
+' sum(case when ioflag=''O'' then sumcount else 0 end ) as OutSumCount, '
+' sum(case when ioflag=''O'' and TradeCodeID in (''%s'') then sumcount else 0 end ) as Outgetcnt, '
+' sum(case when ioflag=''I'' then sumcount else 0 end ) + sum(case when ioflag=''O'' then sumcount else 0 end ) as AllCount '
+' from TxnDataStat a, Bank b, SysAccount c '
+' where a.CurrencyID = b.CurrencyID '
+' and a.BankID = b.BankID '
+' and b.CurrencyID *= c.CurrencyID '
+' and b.SysAccountNo *= c.SysAccountNo '
+' and a.sysdate between ''%s'' and ''%s'' '
+' and a.CurrencyID = ''%s'' '
+' and b.SysAccountNo like ''%s'' '
+' and b.AreaNo like ''%s'' '
+' group by c.SysAccountNo,c.SysBankName,a.CurrencyID,a.Ioflag,a.TradeCodeID '
+' order by c.SysAccountNo,c.SysBankName '
+' SELECT CurrencyID,SysAccountNo,SysBankName, '
+' sum(InSumCount) as InSumCount,sum(Ingetcnt) as Ingetcnt, '
+' sum(case when IOFlag=''I'' then FeeAmount*Ingetcnt else 0 end ) as Inamount, '
+' sum(OutSumCount) as OutSumCount,sum(Outgetcnt) as Outgetcnt, '
+' sum(case when IOFlag=''O'' then FeeAmount*Outgetcnt else 0 end ) as Outamount, '
+' sum(AllCount) as AllCount '
+' FROM @TmpTxnDataStat '
+' group by SysAccountNo,SysBankName,CurrencyID '
+' order by SysAccountNo ',
[sSQL1,sSQL2,sSQL1,sSQL2,sStartDate,sEndDate,sCurrencyID,sSysAccountNo,sAreaNo]);
SQL.Add(sSQL);
Open;
end;
2.如果不行,把sql语句分开执行
编译的时候
declare @TmpTxnDataStat table
这个好像不允许执行
查询的部分使用Open;
或者你干脆就使用存储过程好了(使用TADOStoredProc组件)
查询使用Open
错误:AdoQuery1 CommandText does not return a result set.
(
id int,
txt varchar(20)
);insert into @MyTable (id, txt) values(1, 'ok');select * from @MyTable;每句后面有 ; 分号就行了.