sql server - MsSql Subquery returned more than 1 value -
can me error? msg 512, level 16, state 1, procedure gen048upload, line 101 subquery returned more 1 value. not permitted when subquery follows =, !=, <, <= , >, >= or when subquery used expression.
here code:
alter procedure [dbo].[gen048upload] -- add parameters stored procedure here @filedate varchar(20) begin -- set nocount on added prevent result sets -- interfering select statements. set nocount on; declare @ierrorcode int declare @error int, @tday varchar(2), @tmonth varchar(2), @tyear varchar(4) set @tmonth= month(getdate()) set @tday= day(getdate()) set @tyear= year(getdate()) declare @temptable table ( temprow varchar(1000) ) -- declare @command varchar(1000) -- set @command = 'copy y:\ftp\rmt\' + @filedate + '.txt' -- insert @temptable exec master..xp_cmdshell @command declare @fne_count int set @fne_count = (select count(*) @temptable temprow = 'the system cannot find file specified.') if @fne_count = 0 begin begin tran declare @filename varchar(100) set @filename = @filedate + '.txt' create table #temp1 ( wholerow varchar(8000) ) declare @cmd varchar(8000) set @cmd = 'bulk insert #temp1 ''y:\ftp\rmt\' +@filename+ ''' (datafiletype = ''char'', keepnulls, firstrow=1, maxerrors=0, rowterminator = '''+char(0x0a)+''')' exec(@cmd) set @error=0 set @error = @error + @@error declare @flag bigint,@msg varchar(200) if exists(select isnull(max(flag),0) gen_048) begin set @flag =(select isnull(max(flag),0) gen_048)+1 end else begin set @flag = 1 end -- delete #temp1 wholerow null -- delete #temp1 wholerow '%hostdate|book type|ccy|portfolio|guava |chart code name|input|native debit amount|native credit amount|base amount|counterparty|acct|deal|product|source|native amount%' -- delete #temp1 wholerow '%||||chart code||date|||||no|ref|||%' -- delete #temp1 wholerow = '' declare @inputtable table( input_date varchar(100) ) if exists ( select fdate gen048_fdate fdate in ( select distinct replace(dbo.fn_parsename(wholerow, '|', 8), char(9), '') #temp1 ) ) if exists(select fdate gen048_fdate fdate = @input_date) begin set @msg = 'error: file exists, duplication not allowed' end else begin insert gen048_fdate select distinct replace(dbo.fn_parsename(wholerow,'|',8), char(9), '') #temp1 set @ierrorcode = @@error if (@ierrorcode <> 0) goto problem insert gen_048 ( accname, add1, add2, zip, add3, add4, add5, accdate, bzip, uname, accnum, pin) select dbo.fn_parsename(wholerow,'|',0), dbo.fn_parsename(wholerow,'|',1), dbo.fn_parsename(wholerow,'|',2), dbo.fn_parsename(wholerow,'|',3), dbo.fn_parsename(wholerow,'|',4), dbo.fn_parsename(wholerow,'|',5), dbo.fn_parsename(wholerow,'|',6), dbo.fn_parsename(wholerow,'|',7), dbo.fn_parsename(wholerow,'|',8),-- input date dbo.fn_parsename(wholerow,'|',9), dbo.fn_parsename(wholerow,'|',10), dbo.fn_parsename(wholerow,'|',11) #temp1 set @ierrorcode = @@error if (@ierrorcode <> 0) goto problem set @msg = 'file has been uploaded' end end else begin set @msg = 'file for.' + @filedate + 'not found. no file uploaded.' end commit tran select @msg msg, @filedate filedate problem: if (@ierrorcode <> 0) begin rollback tran select @ierrorcode msg end end end -- [dbo].[gen048upload] '07-07-2015'
if exists ( select fdate gen048_fdate fdate in ( select distinct replace(dbo.fn_parsename(wholerow, '|', 8), char(9), '') #temp1 ) ) update -
alter procedure [dbo].[gen048upload] ( @filedate varchar(20) ) begin set nocount on; if object_id('tempdb.dbo.#temp') not null drop table #temp create table #temp (wholerow varchar(8000)) begin try begin transaction declare @sql nvarchar(max) set @sql = 'bulk insert #temp1 ''y:\ftp\rmt\' + @filedate + '.txt' + ''' (datafiletype = ''char'', keepnulls, firstrow=1, maxerrors=0, rowterminator = ''' + char(0x0a) + ''')' exec sys.sp_executesql @sql if exists ( select fdate gen048_fdate fdate in ( select distinct replace(dbo.fn_parsename(wholerow, '|', 8), char(9), '') #temp1 ) ) begin select 'error: file exists, duplication not allowed', @filedate end else begin insert gen048_fdate (fdate) select distinct replace(dbo.fn_parsename(wholerow,'|',8), char(9), '') #temp1 insert gen_048 (accname, add1, add2, zip, add3, add4, add5, accdate, bzip, uname, accnum, pin) select dbo.fn_parsename(wholerow,'|',0), dbo.fn_parsename(wholerow,'|',1), dbo.fn_parsename(wholerow,'|',2), dbo.fn_parsename(wholerow,'|',3), dbo.fn_parsename(wholerow,'|',4), dbo.fn_parsename(wholerow,'|',5), dbo.fn_parsename(wholerow,'|',6), dbo.fn_parsename(wholerow,'|',7), dbo.fn_parsename(wholerow,'|',8), dbo.fn_parsename(wholerow,'|',9), dbo.fn_parsename(wholerow,'|',10), dbo.fn_parsename(wholerow,'|',11) #temp1 select 'file has been uploaded', @filedate end commit transaction end try begin catch print error_message() if xact_state() <> 0 begin rollback transaction end end catch end
Comments
Post a Comment