Use Alpha characters instead of expanding field

Question

Use Alpha characters instead of expanding field

September 12, 2023 at 12:16 pm

This was removed by the editor as SPAM

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Emperor100 wrote:

Building a new logic may take time plus avoiding certain characters, or keeping a conversion table and when there no resource available, why don't you expand the field itself. Try to expand all the three at once 4,4,4 so the length will be 12.
-- Keep it simple -- Keep incrementing numbers
In my opinion it may not come back and bite until either of the 3 values need a 5 digit number. Check for impact pros & cons.

All such "field expansion" in this case would do is kick the can down the road. Because of growing data, it'll be more difficult to actually fix things in the future. Since they'd have to go through a bit of hell anyway, the should fix it by properly splitting the field back to the original 3 columns.

As a bit of a sidebar, if there was a DBA involved and they didn't raise hell about this decision on prop it up on a stick with the words "Not only no but hell no"! emblazoned on it, they shouldn't be allowed anywhere near a database ever again. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

below86 SSChampion Points: 11532 More actions · Answer 2

We don't want to expand to a 4,4,4 because we would need to update all of the places that use the field to account for the larger size and the values being in different locations within the field.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Yep... there's the "can thing" and understood.

Since values from 000 thru 999 have already been distributed, we can't just use the simplicity of base 36 so I guess I'd do what you suggested near the beginning. It won't necessarily require a lookup table but it will require two separate calculations if the source columns are integer based.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

below86 SSChampion Points: 11532 More actions · Answer 4

We haven't had much added to our back log of work for several months now. So splitting these out into three columns may be something we can tackle in late 2023 or early 2024. But I'm not holding my breath. At least we have it set up as a work around to use the alpha characters for now.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Understood. That's why I suggested that your first take on the subject is probably your best take.

To prevent any overlapping of old vs new, leave everything you currently have alone. Not map table required.

If you do it right, you won't need a map table for anything else, either. Just consider anything from 999 or less to be that literal. Any greater than that would start off with the number 1000 mathematically translated to "AAA". Then do the base 26 or 36 math from there on up.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

below86 SSChampion Points: 11532 More actions · Answer 6

We did go with a conversion table, this way we could eliminate some bad words or abbreviations. I don't think we had anything that would go out with that info on it, but didn't want to take a chance.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

below86 wrote:

We did go with a conversion table, this way we could eliminate some bad words or abbreviations. I don't think we had anything that would go out with that info on it, but didn't want to take a chance.

Aye. Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)