Adding a number to a string to create series

Question

Adding a number to a string to create series

Viewing 5 posts - 16 through 19 (of 19 total)

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

ChrisM@Work (9/5/2014)
Meatloaf (9/3/2014)
Hello,
I trying to figure out the logic to add a number to the end of an ID to create a series.
For example, I have an EventID that may have many sub events.
If the EventID is 31206, and I want to have subEvents, I would like have the following sequence. In this case, lets say I have 4 sub Events so I want to check the EventID and then produce:
312061
312062
312063
312064
How can I check what the EventID is, then concatenate a sequence number by the EventID?
If you pursue this, you will destroy your EventID key. Matching to EventID in any other table will require that you identify which part of EventID is the EventID and which part is the subeventid. If you want to have a subeventid somewhere then create a new column for it. Having said that, creating sequences and subsequences using ROW_NUMBER() is so trivially easy that perhaps you should be evaluating subeventid in code.

Chances are that this serialization is being used outside the scope of the database, ie. enumeration of report or invoice items, who knows?

😎

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Eirikur Eiriksson (9/5/2014)
ChrisM@Work (9/5/2014)
Meatloaf (9/3/2014)
Hello,
I trying to figure out the logic to add a number to the end of an ID to create a series.
For example, I have an EventID that may have many sub events.
If the EventID is 31206, and I want to have subEvents, I would like have the following sequence. In this case, lets say I have 4 sub Events so I want to check the EventID and then produce:
312061
312062
312063
312064
How can I check what the EventID is, then concatenate a sequence number by the EventID?
If you pursue this, you will destroy your EventID key. Matching to EventID in any other table will require that you identify which part of EventID is the EventID and which part is the subeventid. If you want to have a subeventid somewhere then create a new column for it. Having said that, creating sequences and subsequences using ROW_NUMBER() is so trivially easy that perhaps you should be evaluating subeventid in code.
Chances are that this serialization is being used outside the scope of the database, ie. enumeration of report or invoice items, who knows?
😎

Nobody, Mr Viking - but it wasn't explicit from earlier posts in this thread that if it is within the scope of the db, there are easy ways of getting the same functionality without the penalties the OP would experience with the original proposal.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

Eirikur Eiriksson (9/5/2014)
Jeff Moden (9/5/2014)
Eirikur Eiriksson (9/4/2014)
Jeff Moden (9/4/2014)
I guess my question would be, if you recognize it as not being a good practice, why would you post the technique? We just don't know what the OP is going to use this for. I really hope it won't cause some damage down the line for the company that the OP works for.
To clarify, what I consider bad practice is the combining of the keys, hence the warning. The technique has it's proper use in other applications
😎
I agree that the technique has it's proper use in other applications and that you gave a warning but, if the warning isn't heeded, it could be damaging to a company somewhere. Maybe it's just me but I hope we didn't just cause a problem by posting the right answer to the wrong problem.
I recognize that I should have been clearer on the warning, thanks Jeff for highlighting the potential issue.
😎

To be sure, I don't mean to sound like a mother hen. I just worry about other people's data as if it were my own... maybe a bit too much. Thanks for the friendly 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)

TomThomson SSC Guru Points: 104773 More actions · Answer 4

ChrisM@Work (9/5/2014)
Eirikur Eiriksson (9/5/2014)
Chances are that this serialization is being used outside the scope of the database, ie. enumeration of report or invoice items, who knows?
😎
Nobody, Mr Viking - but it wasn't explicit from earlier posts in this thread that if it is within the scope of the db, there are easy ways of getting the same functionality without the penalties the OP would experience with the original proposal.

And besides, even if it is used outside the scope of the db the right way to deal with it will probably be to combine the two key components in the query that passes the data to the outside world, not to hold them combined inside the database.

Tom

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 5

ChrisM@Work (9/5/2014)
Eirikur Eiriksson (9/5/2014)
ChrisM@Work (9/5/2014)
Meatloaf (9/3/2014)
Hello,
I trying to figure out the logic to add a number to the end of an ID to create a series.
For example, I have an EventID that may have many sub events.
If the EventID is 31206, and I want to have subEvents, I would like have the following sequence. In this case, lets say I have 4 sub Events so I want to check the EventID and then produce:
312061
312062
312063
312064
How can I check what the EventID is, then concatenate a sequence number by the EventID?
If you pursue this, you will destroy your EventID key. Matching to EventID in any other table will require that you identify which part of EventID is the EventID and which part is the subeventid. If you want to have a subeventid somewhere then create a new column for it. Having said that, creating sequences and subsequences using ROW_NUMBER() is so trivially easy that perhaps you should be evaluating subeventid in code.
Chances are that this serialization is being used outside the scope of the database, ie. enumeration of report or invoice items, who knows?
😎
Nobody, Mr Viking - but it wasn't explicit from earlier posts in this thread that if it is within the scope of the db, there are easy ways of getting the same functionality without the penalties the OP would experience with the original proposal.

I'm not by any means trying to rape or pillage the forum's high quality of advice, only pointing out that there could be other usage than assumed. Maybe a simple ERD could help explaining the relational solution

😎

+----------+ +-------------+

| Event | | SubEvent |

+----------+ +-------------+

| EventId |-|--, | SubEventId |

| (details)| '---o-<| EventId |

+----------+ | (details) |

+-------------+