从ERP中调取数据,有关发票信息的,但是问题是我在平台上要将若干年的同一个表和在一起,但是ERP里面是按年份为帐套的,所以我导入一个表的时候必须在SBVID字段前面再加一个变量,但是为什么我这样操做 SELECT 2009+A.SBVID AS A.SBVID1 提示SBVID1无效呢?请各位前辈帮忙解决,谢谢。
CREATE PROCEDURE Exp_SpSaleBillAuto
AS
DECLARE @Value VARCHAR(50)
DECLARE @Sql VARCHAR(8000)
DECLARE @DBSign INT
DECLARE @a INTSELECT @Value = [Value], @DBSign = DBSign FROM Pub_Option WHERE [Key]='UFData'SET @a=0SET @Sql = '
INSERT INTO Exp_SaleBill(
SaleBillID
,SaleBillCode
,OutBillCode
,OrderBillCode
,VouchTypeID
,VouchTypeName
,SaleTypeID
,SaleTypeName
,BillDate
,DepName
,PersonName
,CusCode
,CurrencyName
,CurrencyRate
,TaxRate
,MakerName
,BusiTypeName
,DBSign
) SELECT
2009+A.SBVID AS A.SBVID1--流水号
,A.cSbVCode--发票号
,RTRIM(A.cDLCode)
,A.cSoCode
,A.cVouchType --都是普通发票
,''普通发票''
,A.cSTCode--销售类型
,''国外销售''
,A.dDate--开票
,B.Cdepname--销售部门
,C.CpersonName--业务员
,A.cCusCode--客户
,A.cexCh_name--币种
,A.iExchRate--汇率
,A.iTaxRate--税率
,A.cMaker--制单
,A.cBusType--业务类型
,' + CAST(@DBSign AS VARCHAR) + '
FROM ' + @Value + 'SaleBillVouch A
LEFT JOIN ' + @Value + 'Department B ON A.CDepCode = B.CDepCode
LEFT JOIN ' + @Value + 'person C ON A.cPersonCode = C.cPersonCode
WHERE cStCode = 1 AND A.SBVID1 NOT IN(SELECT SaleBillID From Exp_SaleBill WHERE DBSign = ' + CAST(@DBSign AS VARCHAR) + ')'
EXEC(@Sql)
print @SqlSET @Sql = '
INSERT INTO Exp_SaleBillDetail(
DetailID
,SaleBillID
,StoreID
,InvCode
,Qty
,UnitPrice
,TaxUnitPrice
,NoMoney
,TaxMoney
,SumMoney
,DBSign
)
SELECT
AutoID --流水号
,2009+SbVid AS SBVID1--外建
,cWhCode--仓库号
,cInvCode--存货编码
,iQuantity--数量
,iUnitPrice--无税单价
,iTaxUnitPrice --含税单价
,iMoney --无税金额
,iTax --税额
,iSum --价税合计
,' + CAST(@DBSign AS VARCHAR) + '
FROM ' + @Value + 'SaleBillVouchs A
WHERE SBVid In(Select SBVid FROM '+ @Value + 'SaleBillVouch Where cStCode = 1) AND A.AutoID NOT IN(SELECT DetailID From Exp_SaleBillDetail WHERE DBSign = ' + CAST(@DBSign AS VARCHAR) + ')'
EXEC(@Sql)
print @Sql--更新字表中的ID
UPDATE A SET A.ID = B.ID
FROM Exp_SaleBillDetail A
INNER JOIN Exp_SaleBill B ON A.SaleBillID = B.SaleBillID AND B.DBSign = @DBSign
WHERE A.DBSign = @DBSign
GO
CREATE PROCEDURE Exp_SpSaleBillAuto
AS
DECLARE @Value VARCHAR(50)
DECLARE @Sql VARCHAR(8000)
DECLARE @DBSign INT
DECLARE @a INTSELECT @Value = [Value], @DBSign = DBSign FROM Pub_Option WHERE [Key]='UFData'SET @a=0SET @Sql = '
INSERT INTO Exp_SaleBill(
SaleBillID
,SaleBillCode
,OutBillCode
,OrderBillCode
,VouchTypeID
,VouchTypeName
,SaleTypeID
,SaleTypeName
,BillDate
,DepName
,PersonName
,CusCode
,CurrencyName
,CurrencyRate
,TaxRate
,MakerName
,BusiTypeName
,DBSign
) SELECT
2009+A.SBVID AS A.SBVID1--流水号
,A.cSbVCode--发票号
,RTRIM(A.cDLCode)
,A.cSoCode
,A.cVouchType --都是普通发票
,''普通发票''
,A.cSTCode--销售类型
,''国外销售''
,A.dDate--开票
,B.Cdepname--销售部门
,C.CpersonName--业务员
,A.cCusCode--客户
,A.cexCh_name--币种
,A.iExchRate--汇率
,A.iTaxRate--税率
,A.cMaker--制单
,A.cBusType--业务类型
,' + CAST(@DBSign AS VARCHAR) + '
FROM ' + @Value + 'SaleBillVouch A
LEFT JOIN ' + @Value + 'Department B ON A.CDepCode = B.CDepCode
LEFT JOIN ' + @Value + 'person C ON A.cPersonCode = C.cPersonCode
WHERE cStCode = 1 AND A.SBVID1 NOT IN(SELECT SaleBillID From Exp_SaleBill WHERE DBSign = ' + CAST(@DBSign AS VARCHAR) + ')'
EXEC(@Sql)
print @SqlSET @Sql = '
INSERT INTO Exp_SaleBillDetail(
DetailID
,SaleBillID
,StoreID
,InvCode
,Qty
,UnitPrice
,TaxUnitPrice
,NoMoney
,TaxMoney
,SumMoney
,DBSign
)
SELECT
AutoID --流水号
,2009+SbVid AS SBVID1--外建
,cWhCode--仓库号
,cInvCode--存货编码
,iQuantity--数量
,iUnitPrice--无税单价
,iTaxUnitPrice --含税单价
,iMoney --无税金额
,iTax --税额
,iSum --价税合计
,' + CAST(@DBSign AS VARCHAR) + '
FROM ' + @Value + 'SaleBillVouchs A
WHERE SBVid In(Select SBVid FROM '+ @Value + 'SaleBillVouch Where cStCode = 1) AND A.AutoID NOT IN(SELECT DetailID From Exp_SaleBillDetail WHERE DBSign = ' + CAST(@DBSign AS VARCHAR) + ')'
EXEC(@Sql)
print @Sql--更新字表中的ID
UPDATE A SET A.ID = B.ID
FROM Exp_SaleBillDetail A
INNER JOIN Exp_SaleBill B ON A.SaleBillID = B.SaleBillID AND B.DBSign = @DBSign
WHERE A.DBSign = @DBSign
GO
2009+A.SBVID AS A.SBVID1--流水号
不要加A。了吧直接
SELECT
2009+A.SBVID AS SBVID1--流水号
SELECT A AS A1 FROM TB WHERE A1=1
是不对的,应该改为
SELECT A AS A1 FROM TB WHERE A=1
或者
SELECT A1 FROM (
SELECT A AS A1 FROM TB
) T WHERE A1=1
服务器: 消息 207,级别 16,状态 3,行 2
列名 'SBVID1' 无效。 INSERT INTO Exp_SaleBill(
SaleBillID
,SaleBillCode
,OutBillCode
,OrderBillCode
,VouchTypeID
,VouchTypeName
,SaleTypeID
,SaleTypeName
,BillDate
,DepName
,PersonName
,CusCode
,CurrencyName
,CurrencyRate
,TaxRate
,MakerName
,BusiTypeName
,DBSign
) SELECT
2009+A.SBVID AS SBVID1 --流水号
,A.cSbVCode--发票号
,RTRIM(A.cDLCode)
,A.cSoCode
,A.cVouchType --都是普通发票
,'普通发票'
,A.cSTCode--销售类型
,'国外销售'
,A.dDate--开票
,B.Cdepname--销售部门
,C.CpersonName--业务员
,A.cCusCode--客户
,A.cexCh_name--币种
,A.iExchRate--汇率
,A.iTaxRate--税率
,A.cMaker--制单
,A.cBusType--业务类型
,999
FROM UFDATA_999_2010.dbo.SaleBillVouch A
LEFT JOIN UFDATA_999_2010.dbo.Department B ON A.CDepCode = B.CDepCode
LEFT JOIN UFDATA_999_2010.dbo.person C ON A.cPersonCode = C.cPersonCode
WHERE cStCode = 1 AND A.SBVID1 NOT IN(SELECT SaleBillID From Exp_SaleBill WHERE DBSign = 999)(所影响的行数为 0 行)
INSERT INTO Exp_SaleBillDetail(
DetailID
,SaleBillID
,StoreID
,InvCode
,Qty
,UnitPrice
,TaxUnitPrice
,NoMoney
,TaxMoney
,SumMoney
,DBSign
)
SELECT
AutoID --流水号
,2009+SbVid AS SBVID1--外建
,cWhCode--仓库号
,cInvCode--存货编码
,iQuantity--数量
,iUnitPrice--无税单价
,iTaxUnitPrice --含税单价
,iMoney --无税金额
,iTax --税额
,iSum --价税合计
,999
FROM UFDATA_999_2010.dbo.SaleBillVouchs A
WHERE SBVid In(Select SBVid FROM UFDATA_999_2010.dbo.SaleBillVouch Where cStCode = 1) AND A.AutoID NOT IN(SELECT DetailID From Exp_SaleBillDetail WHERE DBSign = 999)(所影响的行数为 0 行)
[/sql]
SaleBillID
,SaleBillCode
,OutBillCode
,OrderBillCode
,VouchTypeID
,VouchTypeName
,SaleTypeID
,SaleTypeName
,BillDate
,DepName
,PersonName
,CusCode
,CurrencyName
,CurrencyRate
,TaxRate
,MakerName
,BusiTypeName
,DBSign
) SELECT
2009+A.SBVID AS SBVID1 --流水号
,A.cSbVCode--发票号
,RTRIM(A.cDLCode)
,A.cSoCode
,A.cVouchType --都是普通发票
,'普通发票'
,A.cSTCode--销售类型
,'国外销售'
,A.dDate--开票
,B.Cdepname--销售部门
,C.CpersonName--业务员
,A.cCusCode--客户
,A.cexCh_name--币种
,A.iExchRate--汇率
,A.iTaxRate--税率
,A.cMaker--制单
,A.cBusType--业务类型
,999
FROM UFDATA_999_2010.dbo.SaleBillVouch A
LEFT JOIN UFDATA_999_2010.dbo.Department B ON A.CDepCode = B.CDepCode
LEFT JOIN UFDATA_999_2010.dbo.person C ON A.cPersonCode = C.cPersonCode
WHERE cStCode = 1 AND 2009+A.SBVID NOT IN(SELECT SaleBillID From Exp_SaleBill WHERE DBSign = 999)问题在那里