Hdr Validation not handling Dupes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Hdr Validation not handling Dupes

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2009-09-27 : 13:19:43

Hi Guys, got a problem.
I cannot get this proc to handle dupes. For instance if a user submits two files with the same seq#.
Im also having a problem with large gaps in the seq#. As long as its a 1 or 2 number diff it works fine. For instance I tested 1151, 1157, 1160, 1161. I got badk 1153, 1154, 1158, 1159.

I am receiving anywhere from 1 to n files daily from several vendors. Each file has a HDR and Trlr. I have managed to validate the Trlr rec but the Hdr is giving me Fits!! Each Hdr contains a seq# and the seq# must be contiguous. Today's hdr seq# must be +1 from yesterday's seq# and all the hdrs recieved today must be contiguous.

The problem I am currently experiencing is: I cannot get this proc to handle dupes. For instance if a user submits two files with the same HDR seq#. The proc is not handling the dupe. I am also having a problem with large gaps in the seq#. As long as it is a 1 or 2 number diff between the seq#s it works fine. For instance I read in 4 files with the following seq#s 1151, 1157, 1160, 1161. The proc wrote the following 1153, 1154, 1158, 1159 numbers to my error table.
I have placed some sample code below. pls let me know your thoughts. I am a relative novice so I am looking for efficient not complicated but code.
Thank you

IF OBJECT_ID('My_Seq_Audit','U') IS NULL --Make sure it doesn't exist before trying to create it
BEGIN
---- CREATE table My_Seq_Audit (Seq_Number int primary key clustered, VendorName varchar(20), Run_Date datetime default getdate(), Vendor_File_Name)
CREATE table My_Seq_Audit (Seq_Number int NOT NULL, VendorName varchar(20) NOT NULL,
Run_Date datetime default getdate()
CONSTRAINT PK_My_Seq_Audit PRIMARY KEY CLUSTERED(Seq_Number, VendorName))
END

DECLARE @run_Date varchar(8)
DECLARE @LasT_Seq as int
DECLARE @MaxSeq as int

set @run_Date = Convert(Varchar(10),Getdate(),112)
Print @run_Date
-- Provide the Last seq# of the prev run date
SET @LasT_Seq = (SELECT MAX(File_Sequence_No) FROM ZZ.T_Hdr
WHERE Vendor_Name = 'CCC' AND RunDate <> @run_Date)
-- Provide Max Seq# for today's hdrs
SET @MAXSeq = (SELECT MAX(File_Sequence_No) FROM ZZ.T_ Hdr
WHERE Vendor_Name = 'CCC' AND RunDate = @run_Date)
print 'initial values CCC'
print @LasT_Seq
print @MaxSeq

WHILE (@LasT_Seq) < @MAXSeq
BEGIN
set @last_seq = @last_seq + 1
print 'increment CCC'
print @LasT_Seq
print 'File Seq#'
SELECT File_Sequence_No FROM ZZ.T_ Hdr WHERE File_Sequence_No = @last_seq
IF not exists (SELECT NULL FROM ZZ.T_ Hdr WHERE File_Sequence_No = @last_seq)
BEGIN
Insert My_Seq_Audit (Run_Date, Seq_Number, VendorName)
select @run_Date, @last_seq, 'CCC'
where not exists (select NULL from My_Seq_Audit where Seq_Number = @Last_Seq)
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources