July 29, 2009 at 11:21 am
I have files coming from several clients, the trailers have to validated against the detail record count. Each client may send as many files as needed. How can I check each trailers record count? I guess my question is what is the equivalent of a “Do Until” in T-SQL?
Also I am getting:
The multi-part identifier "TEX.Enroll_Trlr.Dtl_Count" could not be bound.
DECLARE @Run_Date as int
DECLARE @DTL_CNT as int
SET @Run_Date = Convert(Varchar(10),Getdate(),112)
SET @DTL_CNT = (SELECT count(*)
FROM TEX.Enroll_Dtl, TEX.Enroll_Trlr
WHERE TEX.Enroll_Dtl.RunDate = @Run_Date And TEX.Enroll_Trlr.Vendor_Name = 'CFS'
And TEX.Enroll_Dtl.Vendor_File_Name = TEX.Enroll_Trlr.Vendor_File_Name)
IF @DTL_CNT <> Enroll_Trlr.Dtl_Count
BEGIN
print 'goto QUIT'
Exec TEX.sp_Enrollment_Error_handler
END
SET @DTL_CNT = (SELECT count(*)
FROM TEX.Enroll_Dtl, TEX.Enroll_Trlr
WHERE TEX.Enroll_Dtl.RunDate = @Run_Date And TEX.Enroll_Trlr.Vendor_Name = 'GNA'
And TEX.Enroll_Dtl.Vendor_File_Name = TEX.Enroll_Trlr.Vendor_File_Name)
IF @DTL_CNT <> TEX.Enroll_Trlr.Dtl_Count
BEGIN
print 'goto QUIT'
Exec TEX.sp_Enrollment_Error_handler
END
SET @DTL_CNT = (SELECT count(*)
FROM TEX.Enroll_Dtl, TEX.Enroll_Trlr
WHERE TEX.Enroll_Dtl.RunDate = @Run_Date And TEX.Enroll_Trlr.Vendor_Name = 'SYN'
And TEX.Enroll_Dtl.Vendor_File_Name = TEX.Enroll_Trlr.Vendor_File_Name)
IF @DTL_CNT <> TEX.Enroll_Dtl.Dtl_Count
BEGIN
print 'goto QUIT'
Exec TEX.sp_Enrollment_Error_handler
END
END
July 29, 2009 at 11:30 am
I don't recommend this approach, but you can structure a WHILE loop to act as a DO...UNTIL loop.
WHILE (1 = 1)
BEGIN
--do stuff
.....
-- do more stuff
if (break_condition = true)
BREAK
END
That said - from what you're describing you'd be better off bulk loading to some staging table, and doing your validation there before moving it to the permanent home.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 29, 2009 at 11:52 am
Another alternative would be to use SSIS to import and validate your files prior to loading them in your database.
July 29, 2009 at 12:26 pm
Thanx so much Guys
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply