September 5, 2014 at 5:23 am
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?
π
September 5, 2014 at 5:29 am
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.
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
September 5, 2014 at 7:14 am
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
September 5, 2014 at 9:35 am
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
September 6, 2014 at 7:03 am
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) |
+-------------+
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply