June 18, 2008 at 4:41 pm
I am finishing up a project in which I’m sucking in a file, parsing it into two tables (one header record and multiple detail records), then performing multiple inspections against them. The data has to be 100% clean before I continue with additional processing. The potential error codes are as follows:
create table ErrorCodes (
ErrorCode int primary key,
CodeDesc varchar(50) not null
)
INSERT ErrorCodes
SELECT '0', 'NO ERRORS' union
SELECT '1', 'Batch Count Mismatch' union
SELECT '2', 'Batch Total Mismatch' union
SELECT '4', 'Invalid Org Header' union
SELECT '8', 'Invalid Org Detail' union
SELECT '16', 'Invalid Language Code' union
SELECT '32', 'Invalid Payment Type' union
SELECT '64', 'Invalid CID or Util Acct' union
SELECT '128', 'Negative Pmt Amount' union
SELECT '256', 'Multiple Header Recs' union
SELECT '512', 'Mismatch in Org Codes'
The codes are cumulative, so a code of 7 in the header record indicates mismatch of batch count and batch total, plus an invalid org code. If it’s a code of 4, then it’s only an invalid org code. This way I can see all errors on all records.
Performance isn’t a problem because there will never be more than 20-30 detail records at any given time, and we're only making 2-4 runs a day, so I don't feel to bad if I have to do a loop (or cursor, he says, as he ducks the barrage of arrows flung in his direction).
The temp table is cleared between runs, so the number shouldn't build up in an illogical fashion (7 doubled to 14 is a very different meaning than 7).
This is the code that I was experimenting with to try and decompose the error code value:
declare @codes varchar(1024)
declare @errcode int
select @codes = ''
select @errcode = 7
select @codes = @codes + rtrim(ErrorDesc) + ', '
-- , @errcode = @errcode – ErrorCode
from ErrorCodes
where @errcode >= ErrorCode
order by ErrorCode desc
select @codes
It works fine for 7 and returns the codes for 4, 2, and 1. But if you plug in 32, you should only get 32, instead you get it and everything below it.
The problem is that my brain seems to be on vacation and the rest of me isn’t. I’ve never really done this ‘cumulative string processing’ before, so I’m not really clear on what I’m doing wrong.
The inspection routine that generates the error codes is like this:
print '--Org Code Header'
update TempHeader
set ErrorCode = ErrorCode + 4
from TempHeader h
left outer join Organizations o
on h.OrgCode = o.OrgCode
where o.OrgCode is null
Now, this isn't critical. The fact that any error occurred is enough to prevent the file from loading, but being able to dissect what happened could be useful when we contact the people who generated the file. I can put the system together into production right now without being able to dissect the code if I wanted to.
Any suggestions?
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
June 18, 2008 at 10:19 pm
declare @codes varchar(1024)
declare @errcode int
-- select @codes = '' - it's useless
select @errcode = 7
select @codes = ISNULL(@codes + ', ', '') + rtrim(ErrorDesc) -- get rid of last comma
from ErrorCodes
where @errcode & ErrorCode > 0
order by ErrorCode desc
_____________
Code for TallyGenerator
June 19, 2008 at 8:11 am
Beautiful! Exactly what I needed, especially that [font="Courier New"]@errcode & ErrorCode > 0[/font].
Excellent solution, Sergiy. Thanks very much.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply