Solved

db2 ean13 checkdigit calculate by trigger

Posted on 2010-11-16
22
1,431 Views
Last Modified: 2012-06-21
HI,
I 'd like to calculate by trigger the 13th character of an EAN13 partnumber code.
I did the code in VBA, but now I'd like to translate it in order to
insert it in a db2 trigger (v5r4m0).

I have a table1 with field PN12, and field checkdigit,
how to fill checkdigit with right digit calculated using the code below,
how to transform this code in a trigger?
Function CheckDigitEAN13(PN12 As String) As Integer
    
    Factor = 3
    WeightedTotal = 0
    For I = Len(PN12) To 1 Step -1
        'Get the value of each number starting at the end
        CurrentCharNum = Mid(PN12, I, 1)
        'Multiply by the weighting factor which is 3,1,3,1...
        'and add the sum together
        WeightedTotal = WeightedTotal + CurrentCharNum * Factor
        'Change factor for next calculation
        Factor = 4 - Factor
    Next I
    'Find the CheckDigit by finding the number + WeightedTotal that = a multiple of 10
    'Divide by 10, get the remainder and subtract from 10
    I = (WeightedTotal Mod 10)
    If I <> 0 Then
        CheckDigit = (10 - I)
    Else
        CheckDigit = 0
    End If

    CheckDigitEAN13 = CheckDigit

End Function

Open in new window

0
Comment
Question by:bobdylan75
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
22 Comments
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 100 total points
ID: 34146877
the best solution would be to create your own function that does that
take a look here
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstcfsc.htm#cfsc
and
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstsqlcontstmts.htm#sqlcontstmts

to get an idea how this can be done

then, in the trigger you just call your function like any other function
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 34146887
Hi Bob,

If you're familiar with SQL coding, it's pretty easy.  :)  The basics of a Trigger are wrapped around the calculation.  Because of the coding limitations of triggers, it might be easiest to write the CheckDigit calculation as a UDF, then call the function within the trigger.

What do you want to do with the check-digit?  Append it to a string or store it into its own column?


Kent
0
 

Author Comment

by:bobdylan75
ID: 34147068
Hi Kdo,
I want to append it to the PartNumber 12 chars so I have my EAN13 code
and I can print label with barcode..
I've only a little experience with trigger and I never use UDF before, can you explein my better?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 27

Expert Comment

by:tliotta
ID: 34152115
...in order to insert it in a db2 trigger

A "trigger" doesn't seem appropriate. By putting the code into a trigger, the code will execute every time the trigger event (INSERT or UPDATE) happens -- even if the update program doesn't need it to run.

It would be reasonable to have a trigger verify that a check-digit was correct, and possibly even to replace invalid check-digits with correct ones (though I'd question the wisdom of such a trigger). But it doesn't seem quite right in this case. It could fire even if someone was updating columns that didn't require the credit card number itself.

And I'm not totally clear if a UDF makes sense, though it seems possibly more sensible than a trigger.

I can give you a UDF that accepts a 12-char number and returns a 13-char version, or returns just the check digit as a character or an integer. But I'm wondering where you'd put any of it to use.

What is the work-flow? Are you somehow receiving a file with just the 12-char numbers and you need to convert them? Can you describe the process? Maybe the best solution is at some other step.

Tom
0
 

Author Comment

by:bobdylan75
ID: 34153492
The user insert an new internal part number for a new product they sell.
So I transform this new part number in a 12 chars part number for ean13.
So I have to create the ean13 code, just adding the 13th character.
Now I made it on Windows PC.
I want to use online db2.
I understand that I have to learn UDF and so to use this UDF function
on a simple trigger Insert/update.
It's this right?
0
 

Author Comment

by:bobdylan75
ID: 34153496
(the 13th character is the checkdigit character calculated with the vb code before.)
0
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 34172771
Do you need this calculation in SQL or in a RPG-Trigger program,
I have both of them for you.... the SQL is complex to read, the RPG I have to dig in our system :-)

0
 

Author Comment

by:bobdylan75
ID: 34172912
I'd like, if is possible, to have first RPG one ;) thanks..
0
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 34187532
Here a working example of the EAN controll digit Inputparm = 13 pos Character,
the return value is the complete barcode.
 *-------------------------------------------------------------------------  
 * Fields definitions                                                        
 *-------------------------------------------------------------------------  
D EAN13           DS            13                                           
D EAN                            1A   DIM(13)                                
D CALC            S              5S 0                                        
D CTRL            S              1S 0                                        
 *-------------------------------------------------------------------------  
 * Main line                                                                 
 *-------------------------------------------------------------------------  
C                   EVAL      CALC =                                         
C                                 %DEC(EAN(02):1:0) + %DEC(EAN(04):1:0) +    
C                                 %DEC(EAN(06):1:0) + %DEC(EAN(08):1:0) +    
C                                 %DEC(EAN(10):1:0) + %DEC(EAN(12):1:0)      
                                                                             
C                   EVAL      Calc = Calc * 3                                
                                                                             
C                   EVAL      CALC = CALC +                                  
C                                 %DEC(EAN(01):1:0) + %DEC(EAN(03):1:0) +    
C                                 %DEC(EAN(05):1:0) + %DEC(EAN(07):1:0) +  
C                                 %DEC(EAN(09):1:0) + %DEC(EAN(11):1:0)    
                                                                           
C                   EVAL      Ctrl    = 10 - %Rem(Calc:10)                 
C                   EVAL      EAN(13) = %editc(Ctrl:'X')                   
                                                                           
C                   EVAL      *InLr = *On                                  
 *-------------------------------------------------------------------------
 * Klist + Plist definitions                                               
 *-------------------------------------------------------------------------
C     *ENTRY        PLIST                                                  
C                   PARM                    EAN13                          

Open in new window



Good Luck
Murph
0
 

Author Comment

by:bobdylan75
ID: 34187565
Sorry but thanks for the rpgle program,
but I don't know how to implement the trigger to link the rpgle program,
because this is a simple rpgle program at the moment.
0
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 34187850
That is what you asked for

"I'd like, if is possible, to have first RPG one ;) thanks.."
0
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 34187911
You can call this from a trigger program, but beware there is NO check on the contents of the input parm,
so non nuneric data gives errors, how sure are you that the data in the table is numeric or filled with numeric fields only.

I understand you like one of us to write the trigger program for you?

0
 

Author Comment

by:bobdylan75
ID: 34187954
Exactly.. ;-)
My question was how to write a trigger ...
I Don't now the syntax to call the rpgle program and all the rest..
My first question was to use only sql but seen that I currently use rpg
I think that is a good idea use rpg but now
remains to understand how to call the rpg program from the trigger.
0
 

Author Comment

by:bobdylan75
ID: 34187965
the data control is a my problem ;))
0
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 34188132
Give me the file name you like to trigger and the field name of the EANcode.

0
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 34188194
Oh and tell me what must be done if the field is not filled with 12 or 13 digits or the digits are not numeric

0
 

Author Comment

by:bobdylan75
ID: 34188240
Thank you very very very much Murphey!
to semplify:
table name: EanTable.
Field 12 chars name: PartNumber12
Field 13 chars name: PartNumber13

If PartNumber12 is empty or has not 12 chars or is not a number, it could write in PartNumber13 a value like "ERROR".

Thank you very much!
0
 
LVL 16

Accepted Solution

by:
theo kouwenhoven earned 400 total points
ID: 34188678
Hi bobdylan75,

Attached you find 2 sources 1 is checking the barcode  the other is the trigger.

You have to add the trigger twice one time for Insert and one time for update
both *before  and with parameter ALWREPCHG(*YES)

Be carefull, becaus I don't have your file here, I was not able to test, so applie
it first in a test environemt and change the fields withe.g. DFU for full controll

Good luck
BAR13CD.txt
TRG001R.txt
0
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 34204149
Hey Hi bobdylan75,

How are you dooing?

Is it up and running?


0
 

Author Comment

by:bobdylan75
ID: 34205829
;)
sorry I was away..
wow but TRG001R is a trigger made with rpg?
I have to compile it like a simple rpgle program?
Tomorrow I will test it. Today I cannot sorry.
0
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 34206369
Yes just compile is like all other RPG...
(The other program too) :)


0
 

Author Closing Comment

by:bobdylan75
ID: 34212515
thank you so much! in the next days I will be here for a consult again
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question