易神乐邦技术论坛

标题: 从数据类型varchar转换为numeric时出错。警告:聚合或其他SET... [打印本页]

作者: hd87715 时间: 2017-12-20 00:22
标题: 从数据类型varchar转换为numeric时出错。警告:聚合或其他SET...
从数据类型varchar转换为numeric时出错。警告:聚合或其他SET操作消除了NULL值
[attach]210[/attach]

这个一般是启用了条码自动生成的触发启造成的。[attach]211[/attach]

更改条码，由于客户的条码位数过长所以需要生成新的条码信息，使用以下函数生成条码，并且加入触发器进行反写,同时加入日志记录每次变更条码的款式信息
日志表（bas_prd_barcodeLogInfo）
CREATE TABLE [dbo].[bas_prd_barcodeLogInfo](
[ID] [nvarchar](50) NOT NULL,
[fas_id] [nvarchar](20) NULL,
[col_id] [nvarchar](20) NULL,
[siz_id] [nvarchar](20) NULL,
[prd_barcode] [nvarchar](20) NULL,
[changeDatetime] [nvarchar](19) NULL,
   CONSTRAINT [PK_bas_prd_barcodeLogInfo] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[bas_prd_barcodeLogInfo] ADD  CONSTRAINT [DF_bas_prd_barcodeLogInfo_ID]  DEFAULT (newid()) FOR [ID]
GO

ALTER TABLE [dbo].[bas_prd_barcodeLogInfo] ADD  CONSTRAINT [DF_bas_prd_barcodeLogInfo_changeDatetime]  DEFAULT (CONVERT([nvarchar](19),getdate(),(120))) FOR

[changeDatetime]
GO

函数
create FUNCTION GenerateBarcode()
RETURNS  nvarchar(8)
AS
begin
DECLARE @barcodeMax DECIMAL(18,0)
SET @barcodeMax = 0
SELECT @barcodeMax = MAX(prd_barcode) FROM dbo.BAS_PRD WHERE LEN(prd_barcode) = 8 AND PATINDEX('%[^0-9]%',prd_barcode) = 1 and SUBSTRING

(prd_barcode,1,1) = '1'
IF(@barcodeMax=0 OR @barcodeMax IS NULL)
BEGIN
SET @barcodeMax = 10000001
END

      return REPLICATE('0',8-len((@barcodeMax+1)))+cast((@barcodeMax+1) as varchar)
end

触发器
CREATE  TRIGGER [dbo].[TR_BAS_PRD_Insert] ON [dbo].[BAS_PRD]
FOR INSERT
AS

DECLARE basPrdCursor CURSOR
      FOR
SELECT prd_id,fas_id,col_id,siz_id  FROM Inserted
OPEN basPrdCursor

DECLARE @prd_id NVARCHAR(20),@fas_id NVARCHAR(20),@col_id NVARCHAR(20),@siz_id NVARCHAR(20),@prdbarcode nvarchar(20)
      FETCH NEXT FROM  basPrdCursor INTO @prd_id,@fas_id,@col_id,@siz_id
WHILE @@FETCH_STATUS =0
      BEGIN
select @prdbarcode = dbo.GenerateBarcode()
         UPDATE dbo.BAS_PRD SET prd_barcode = @prdbarcode WHERE prd_id = @prd_id
         insert into bas_prd_barcodeLogInfo(fas_id,col_id,siz_id,prd_barcode)
         values(@fas_id,@col_id,@siz_id,@prdbarcode)
         FETCH NEXT FROM  basPrdCursor INTO @prd_id,@fas_id,@col_id,@siz_id
      END

--关闭游标
CLOSE basPrdCursor
--释放资源
DEALLOCATE basPrdCursor
go
-----------------------------------------------------------我是分割线----------------------------------------------------------------------------
更新的代码
alter FUNCTION GenerateBarcode()
RETURNS  nvarchar(8)
AS
begin
DECLARE @barcodeMax DECIMAL(18,0)
SET @barcodeMax = 0
SELECT @barcodeMax = MAX(prd_barcode) FROM dbo.BAS_PRD WHERE LEN(prd_barcode) = 8 AND PATINDEX('%[^0-9]%',prd_barcode) = 1 and SUBSTRING

(prd_barcode,1,1) = '1'
IF(@barcodeMax=0 OR @barcodeMax IS NULL)
BEGIN
SET @barcodeMax = 10000001
END

      return REPLICATE('0',8-len((@barcodeMax+1)))+cast((@barcodeMax+1) as varchar)
end

欢迎光临易神乐邦技术论坛 (http://bbs.lebang360.com/)