Dissecting a cumulative error code

  • 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 (

    ErrorCodeintprimary key,

    CodeDescvarchar(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]

  • 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

  • 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