Sub Account - Sub Query Problem

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[H_DeleteDuplicate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[H_DeleteDuplicate]

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE procedure H_DeleteDuplicate

@TransDate datetime -- in the format 'yyyy/mm/dd'

as

declare @Qry as varchar(2000)

declare @FileDate as varchar(6)

--use subaccounts

set @FileDate = replace(convert(varchar(10), @TransDate, 3), '/', '')

--Adding a new column TransId which will help in deleting the duplicate row

set @Qry = 'sp_columns @Table_Name = ''SosummaryB' + @FileDate + ''', @Column_Name = ''TransID'''

execute(@Qry)

if @@rowcount = 0

begin

set @Qry = 'Alter table SoSummaryB' + @FileDate

set @Qry = @Qry + ' ADD TransID numeric identity(1,1)'

execute(@Qry)

end

--Now deleting the duplicate row which has the same socode and same

--transdate in the table sosummaryBddmmyy

set @Qry = 'delete from SosummaryB' + @FileDate + ' where '

set @Qry = @Qry + ' TransID in '

set @Qry = @Qry + ' (Select max(TransID) from SosummaryB' + @FileDate

set @Qry = @Qry + ' group by SoCode, TransDate having count(SoCode) > 1)'

execute (@Qry)

--Adding a new column TransId which will help in deleting the duplicate row

set @Qry = 'sp_columns @Table_Name = ''SosummaryA' + @FileDate + ''', @Column_Name = ''TransID'''

execute(@Qry)

if @@rowcount = 0

begin

set @Qry = 'Alter table SoSummaryA' + @FileDate

set @Qry = @Qry + ' ADD TransID numeric identity(1,1)'

execute(@Qry)

end

--Now deleting the duplicate row which has the same socode and same transdate

--in the table sosummaryAddmmyy

set @Qry = 'delete from SosummaryA' + @FileDate + ' where '

set @Qry = @Qry + ' TransID in '

set @Qry = @Qry + ' (Select max(TransID) from SosummaryA' + @FileDate

set @Qry = @Qry + ' group by SoCode, TransDate having count(SoCode) > 1)'

execute (@Qry)

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

READ ME AS FOLLOWS :

For Error in Daybegin or Additional Daily in SUBACCOUNTS Application (For Both SubOffice related and BranchOffice related)

-------------------------------------------------------------------

Solution for the problem "SubQuery returned more than one value...." and for Two OBs for same dates to same office under Opening Balance option of Supervisor.

Steps:

1. Download the Script file SUBQUERYPROBLEM.SQL from solution scripts folder

2. Open Query Analyser

3. Select SUBACCOUNTS database if error is coming while entering SO Additional Daily Account

else select BOSUBACCOUNTs if error is found in BO Additional Daily Account.

4. open the downloaded file and run it by pressing F5

5. open a new QUERY WINDOW

6. type the following statement which has the SOSUMMARY Date (NOT DAILY A/C DATE). Pls Note the date has to be in the same format of yyyy/mm/dd.

NOTE : If the error occurs for daybegin then the SOSUMMARY date should be the previous date for which Dayend is already done.

Execute H_DeleteDuplicate '2004/02/25'

7. Run it by pressing F5

8. Close Query Analyser and start working in SubAccounts Application