DB2临时表无法创建的问题的确很让我们头疼,下面就为您介绍一个解决DB2临时表无法创建问题的方法,希望对您学习DB2临时表方面能有所帮助。
class="dp-xml">
- procedure TXXXForm.lc_procHandleStandard(strpo:string; xPOSEQ:Integer);//HXYADD----24/09/2010----
- var
- strFieldList:string;//A-JW-02-351101-01
- begin
- strFieldList:='XNAME VARCHAR(80),'#13#10
- +'XKIND VARCHAR(20),'#13#10
- +'ENAME VARCHAR(20),'#13#10
- +'CNAME VARCHAR(20),'#13#10
- +'XBIAO VARCHAR(80),'#13#10
- +'XCODE INTEGER,'#13#10
- +'XGRADE INTEGER ';
- strTmpTableName:=lc_CreatTmpTable(strFieldList);
- if strTmpTableName='' then
- begin
- Messagebox(handle,'无法创建 [执行标准资料] 临时表﹗','提示信息',mb_Ok);
- Exit;
- end;//*)
- qry6.Close;
- qry6.SQL.Clear;
- qry6.SQL.Add('DELETE FROM '+strTmpTableName);
- qry6.ExecSQL;
- qry6.Close;
- qry6.SQL.Clear;
- qry6.SQL.Add('INSERT INTO '+strTmpTableName);
- qry6.SQL.Add('(XNAME,XKIND,ENAME,CNAME,XBIAO,XCODE,XGRADE)');
- qry6.SQL.Add('SELECT VALUE(K.PROD_NAME,''''),');
- qry6.SQL.Add(' CASE K.PROD_KIND WHEN 0 THEN ''0 原色产品'' ');
- qry6.SQL.Add(' WHEN 1 THEN ''1 水洗产品'' ');
- qry6.SQL.Add(' ELSE ''X 不分类'' END PROD_KIND,');
- qry6.SQL.Add(' VALUE(A.COLOR_ENG_NAME,''''),');
- qry6.SQL.Add(' VALUE(A.COLOR_CHN_NAME,''''),');
- qry6.SQL.Add(' VALUE(K.ZHIXIN_BIAO,''''),');
- qry6.SQL.Add(' K.S_CODE,');
- qry6.SQL.Add(' A.COLOR_GRADE ');
- qry6.SQL.Add(' FROM GSSAL.SAL_PO_DTLA A ');
- qry6.SQL.Add(' LEFT JOIN GSSAL.SAL_PO X ON (X.PO_NO=A.PO_NO AND X.GEN_POSEQ=A.GEN_POSEQ) ');
- qry6.SQL.Add(' LEFT JOIN GSSAL.SAL_KIND K ON (K.S_CODE=VALUE(A.PROD_CODE,X.PROD_CODE)) ');
- qry6.SQL.Add(' WHERE A.PO_NO=:PO_NO AND A.GEN_POSEQ=:GEN_POSEQ');
- qry6.SQL.Add(' AND VALUE(K.ISVALID,''N'')=''Y'' ');
- qry6.SQL.Add(' ORDER BY K.PROD_NAME,A.COLOR_ENG_NAME ');
- qry6.ParamByName('PO_NO').AsString:=strpo;
- qry6.ParamByName('GEN_POSEQ').AsInteger:=xPOSEQ;
- qry6.ExecSQL;
- qry6.Close;
- qry6.SQL.Clear;
- qry6.SQL.Add('SELECT * ');
- qry6.SQL.Add('FROM '+strTmpTableName);
- qry6.Open;
- end;
- -----------------------------------------------------------------------------------------------------------------------------------
- function TXXXForm.lc_CreatTmpTable(strFieldList:string):string;
- var
- strTableName:string;
- blnSuccess:boolean;
- i:integer;
- function lc_funcCreatTable(strFieldList,strTableName: string):Boolean;
- var
- qryCreatTemp:TQuery;
- begin
- Result:=False;
- qryCreatTemp:=TQuery.Create(self);
- with qryCreatTemp do
- begin
- DatabaseName:='GSCOMDB';
- SQL.Clear;
- SQL.Add('CREATE TABLE '+strTableName+' ('+strFieldList+') IN USERSPACE1 ');
- try
- ExecSQL;
- Result:=True;
- except
- end;
- end;
- qryCreatTemp.Free;
- end;
- begin
- Result:='';//如果创建不成功则返回空字符串。
- strTableName:='GSTMP.TMP_'+FormatFloat('0',now*10000000)+'';
- for i:=1 to 50 do//循环 50次﹐再不能创建则退出。
- begin
- blnSuccess:=lc_funcCreatTable(strFieldList,strTableName);
- if blnSuccess then //如果创建成功则返回表名﹐同时退出。
- begin
- Result:=strTableName;
- Exit;
- end;
- end;
- end;
- -----------------------------------------------------------------------------------------------------------------------------------
- procedure TXXXForm.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
- begin
- lstUser.Free;
- if strTmpTableName<>'' then
- begin
- lc_DeleteTable(strTmpTableName);
- strTmpTableName:='';
- end;
- end;
- -----------------------------------------------------------------------------------------------------------------------------------
- function TXXXForm.lc_DeleteTable(strTableName:string):Boolean;
- var
- qryDeleteTemp:TQuery;
- begin
- Result:=False;
- qryDeleteTemp:=TQuery.Create(Self);
- with qryDeleteTemp do
- begin
- DatabaseName:='GSCOMDB';
- SQL.Clear;
- SQL.Add('DROP TABLE '+strTableName);
- try
- ExecSQL;
- Result:=True;
- except
- end;
- end;
- qryDeleteTemp.Free;
- end;
- -----------------------------------------------------------------------------------------------------------------------------------