Issue:
When trying to edit a customer the system returns an error that the subquery returned more than one value when clicking save.
Solution:
This is usually due to duplicate zip code records in the GE_Table3
The SQL script below will create a stored procedure in the SedonaOffice databases that can then be run if the problem occurs.
Open SQL Management Studio.
Connect to the SQL server using the SedonaUser login.
Once connected, expand the Databases folder
Right click on the Sedona Database and select New Query an
Copy and paste the contents of the script below into the query window.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Scott Pickens
-- Create date: 06/11/2024
-- Description: GE3DuplicateFix Remove duplicate Zip Codes and Update existing records with correct ID
-- =============================================
CREATE PROCEDURE dbo.GE3DuplicateFix
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Removes duplicate zip codes and points entities to the correct zip entry.
declare @duplicates table (DuplicateId int, CorrectId int);
insert into @duplicates (DuplicateId, CorrectId)
select distinct dups.Table3_ID as DuplicateId, base.Table3_ID as CorrectId
from GE_Table3 ge
cross apply
(
select top 1 Table3_ID
from GE_Table3
where [Description] = ge.[Description] and Country_ID = ge.Country_ID
order by Table3_ID
) as base
cross apply
(
select Table3_ID
from GE_Table3
where [Description] = ge.[Description] and Country_ID = ge.Country_ID and Table3_ID > base.Table3_ID
) as dups;
if @@rowcount > 0
begin
select * from @duplicates;
if exists (select 1 from sys.tables where [name] = 'AP_Vendor')
begin
update x set GE_Table3_Id = dup.CorrectId
from AP_Vendor x
join @duplicates dup on dup.DuplicateId = x.GE_Table3_Id;
update x set Pymt_GE_Table3_Id = dup.CorrectId
from AP_Vendor x
join @duplicates dup on dup.DuplicateId = x.Pymt_GE_Table3_Id;
end
if exists (select 1 from sys.tables where [name] = 'AR_Customer_Bill')
begin
update x set GE_Table3_Id = dup.CorrectId
from AR_Customer_Bill x
join @duplicates dup on dup.DuplicateId = x.GE_Table3_Id;
end
if exists (select 1 from sys.tables where [name] = 'AR_Customer_Site')
begin
update x set GE_Table3_Id = dup.CorrectId
from AR_Customer_Site x
join @duplicates dup on dup.DuplicateId = x.GE_Table3_Id;
end
if exists (select 1 from sys.tables where [name] = 'IN_Warehouse')
begin
update x set GE_Table3_Id = dup.CorrectId
from IN_Warehouse x
join @duplicates dup on dup.DuplicateId = x.GE_Table3_Id;
end
if exists (select 1 from sys.tables where [name] = 'KS_OPTWebServices_Avalara_Tax_Table_Linking')
begin
update x set GE_Table3_Id = dup.CorrectId
from KS_OPTWebServices_Avalara_Tax_Table_Linking x
join @duplicates dup on dup.DuplicateId = x.GE_Table3_Id;
end
if exists (select 1 from sys.tables where [name] = 'KS_SedonaSync_GE_Table3')
begin
update x set GE_Table3_Id = dup.CorrectId
from KS_SedonaSync_GE_Table3 x
join @duplicates dup on dup.DuplicateId = x.GE_Table3_Id;
end
if exists (select 1 from sys.tables where [name] = 'SV_Service_Company')
begin
update x set GE_Table3_Id = dup.CorrectId
from SV_Service_Company x
join @duplicates dup on dup.DuplicateId = x.GE_Table3_Id;
end
if exists (select 1 from sys.tables where [name] = 'SV_Service_Tech')
begin
update x set GE_Table3_Id = dup.CorrectId
from SV_Service_Tech x
join @duplicates dup on dup.DuplicateId = x.GE_Table3_Id;
end
delete ge
from GE_Table3 ge
join @duplicates dup on dup.DuplicateId = ge.Table3_ID;
END
END
GO
---------------------------------------------------------------------------------
Once finished you should see the Command completed successfully message.
You can now run this stored procedure from SSMS or from within the SQL window in SedonaOffice.
From a query window execute the command below.
exec GE3DuplicateFix
If using the SQL window in Sedona Office be sure to add the // in front of the statement.
// exec GE3DuplicateFix
You should see the message that it completed.
