You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

AutoIncrement of AlphaNumeric Transaction Number

3 replies [Last post]
venkat.srinivas18's picture
Joined: 07/16/2008
Posts: 6

Suppose Say we have

Transaction Id :                        QC0001
This Code Will give output as : QC0002
                                                      .
                                                      .
                                                 
-----------------------------------------

Local number &qcno;

If %Component = Component.CB_QC_CMP And
      %Mode = "A" Then
   
   CB_QC_TBL.CB_QC_NO.Enabled = False;
   SQLExec("SELECT MAX(SUBSTR(CB_QC_NO,3,4)) FROM PS_CB_QC_TBL", &qcno);
   &qcno = Int(&qcno) + 1;
   &DECIMAL = 4 - Len(String(&qcno));
   If &DECIMAL < 0 Then
      Error ("FIELD LENGTH EXCEEDS REQUIRED LENGTH.");
   Else
      &myvalue = NumberToString("%04", &qcno);
      CB_QC_TBL.CB_QC_NO.Value = "QC" | &myvalue
   End-If;
   
End-If;
--------------

Any Suggestions Revert Back to me

Regards,
Venkat

Lepa's picture
User offline. Last seen 2 years 13 weeks ago. Offline
Joined: 06/23/2008
Posts: 591
Re: AutoIncrement of AlphaNumeric Transaction Number

Thanks for sharing your code with us. You should be able to share your knowledge with us by posting them as "code sample" or "article". Login and then click on create content and in this example you would want to choose "code sample".

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Larry's picture
User offline. Last seen 3 years 12 weeks ago. Offline
Joined: 03/18/2008
Posts: 19
Re: AutoIncrement of AlphaNumeric Transaction Number

I have code to auto-increment an alphanumeric field, using SQL calls. It is specific to a 4-character field, but can easily be adapted to different length fields.

SQLExec("SELECT decode(substr(oldvalue,2,3),'ZZZ', TRANSLATE(UPPER(SUBSTR(oldvalue,1,1)),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0'),substr(oldvalue,1,1)) ||decode(substr(oldvalue,3,2),'ZZ',TRANSLATE(UPPER(SUBSTR(oldvalue,2,1)), '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0'),substr(oldvalue,2,1))||decode(substr(oldvalue,4,1),'Z', TRANSLATE(UPPER(SUBSTR(oldvalue,3,1)), '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0'),substr(oldvalue,3,1))||TRANSLATE(UPPER(SUBSTR(oldvalue,4,1)), '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0') FROM dual, (select :1 as oldvalue from dual)", &priorvalue, &newvalue);

If &newvalue = "0000" Then
   &bOverrun_Error = True;
End-If;

Lepa's picture
User offline. Last seen 2 years 13 weeks ago. Offline
Joined: 06/23/2008
Posts: 591
Re: AutoIncrement of AlphaNumeric Transaction Number

Hey Larry, thanks for sharing your code with us. I have added your above post to your blog at http://www.compshack.com/blog/larry - Please feel free to share more with us anytime using your own blog. In doing so, your posts will get more exposure in the community and thus provides a much better value :)

Thanks again - cheers!

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog