September 27, 2009 at 11:14 am
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 'incrementCCC'
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
September 27, 2009 at 1:30 pm
Although a little quirky, I think the proc should work fine - it seems to me that you've just missed the vendor restriction near the end...
SELECT File_Sequence_No FROM ZZ.T_ Hdr WHERE File_Sequence_No = @last_seq
Should possibly be
SELECT File_Sequence_No FROM ZZ.T_ Hdr WHERE File_Sequence_No = @last_seq and Vendor_Name = 'CCC'
However, Lynn Pettis posted a good article the other day about creating a tally function - you should look at that and try to refactor your code to use a tally function for the sequence numbers you're looking for, and left join from that to your sequence table.
I'm not sure how you would want to handle dupes. But it's most probably going to involve selecting out of your audit table before you process a given file to ensure that the file seq # has not already been processed.
Hope that helps. 🙂
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 28, 2009 at 12:34 pm
Thank you so much Matt for responding so quickly.
As for the Dupes, I decided to load all of the incoming seq #'s to a table and run a dupe query. It may not be pretty but it works.
Thanks so much again
September 28, 2009 at 1:08 pm
Really not a problem 😀
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply