1 (edited by boxygen 06/08/2018 06:35:10 pm)

Topic: Account Code to be define as int

In chart_master table if I change the data type of account_code field from Varchar to int, will it cause any anomaly in relationships with other tables?

I have kept all Account Codes as Integer

www.boxygen.pk

Re: Account Code to be define as int

Hello @boxygen

Yes it will. There are many tables where there are references to the account number, so please do not change this. Why do you want to change from varchar to int. The reason for using varchar is how the account numbers are sorted and there might be a wish in some countries to have them alphanumeric.
In most serious accounting systems this variable is varchar.

Joe

Re: Account Code to be define as int

Thanks @Joe,

Yes I understand, but Actually I needed a customization to automatically pick the Next Available Account Code for the Given Account Type. It worked Fine till Code 2899 but when it reaches 28100 it is not picking the next value 28101 because the order sequence of Varchar says 28100 is smaller than 2899.

Converting it to INT  is working fine but I fear it may cause anomalies. Any Ideas?

www.boxygen.pk

4 (edited by poncho1234 06/09/2018 06:10:24 pm)

Re: Account Code to be define as int

May not solve your problem as used for ordering, but could help:-

stack-overflow mysql order varchar field as integer not sure how you could use this to select next, but maybe you can?

More here @ DBA SE pad the front with zeros or use CAST

Another one here uses a different method to sort by length 1st... the article also mentions a php function natsort() which may be worth investigating

Re: Account Code to be define as int

@poncho1234

Stack Over Flow natsort() solution worked like a charm. Thanks A Lot.

www.boxygen.pk