T-SQL Query Help

Question

T-SQL Query Help

Viewing 14 posts - 16 through 28 (of 28 total)

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

Neil Burton SSC-Insane Points: 22999 More actions · Answer 1

rjhare wrote:

Thanks Neil,
I assumed this level of disdain, to those less experienced, had disappeared twenty years ago from technical forums - apparently not.
I will not be asking too many questions in anticipation of such abuse...sadly.
Rich

You're welcome, in both senses. Do not let Mr Celko put you off asking questions here. In no way does he represent even a minority of users. In fact he's the only user who treats people, especially newcomers, in that way. He seems to believe that because of his experiences in developing the standards for SQL, not just T-SQL, all SQL, he can treat people badly. Unfortunately his attitude results in people like you deciding they'd rather ask questions elsewhere. I can't blame you but I'm going to reiterate that he's a one off. Most people are much more welcoming. My advice is just ignore him.

This reply was modified 4 years, 10 months ago by Neil Burton.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

x SSC-Insane Points: 23660 More actions · Answer 2

Neil Burton wrote:

rjhare wrote:
Thanks Neil,
I assumed this level of disdain, to those less experienced, had disappeared twenty years ago from technical forums - apparently not.
I will not be asking too many questions in anticipation of such abuse...sadly.
Rich
You're welcome, in both senses. Do not let Mr Celko put you off asking questions here. In no way does he represent even a minority of users. In fact he's the only user who treats people, especially newcomers, in that way. He seems to believe that because of his experiences in developing the standards for SQL, not just T-SQL, all SQL, he can treat people badly. Unfortunately his attitude results in people like you deciding they'd rather ask questions elsewhere. I can't blame you but I'm going to reiterate that he's a one off. Most people are much more welcoming. My advice is just ignore him.

Forums behave better with crowdsourced moderation. When you don't have that, it turns into the wild west where the only remediation is manual intervention by administrators.

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

rjhare wrote:

Thanks Neil,
I assumed this level of disdain, to those less experienced, had disappeared twenty years ago from technical forums - apparently not.
I will not be asking too many questions in anticipation of such abuse...sadly.
Rich

Hi, Rich,

I don't work for RedGate nor SQLServerCentral. I'm just a frequent user like many of the good folks on these forums. Neil is absolutely correct. Please don't let the actions of one or even a few deter you. There are some absolutely great people with incredible knowledge on these forums and they will sometimes take literally days of their own time to help folks out with some rather remarkable ideas and code whether they're newbies to something or not.

--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)

oogibah SSC Eights! Points: 827 More actions · Answer 4

Can confirm with Jeff, sorry if I sounded a bit salty up there 🙂

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

oogibah wrote:

Can confirm with Jeff, sorry if I sounded a bit salty up there 🙂

You have nothing to apologize about. I appreciated the humor about the # sign. I've never heard it called an "octothorp", either. In fact, it wasn't until I started working with computers that I ever heard someone call it a "hash" before. 😀

--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)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

@rocky_498 ,

Trying to get this thread back on track for your original question... is there an answer to my inquiry so we can get busy with answering your question with some code?

Here's the question again...

Jeff Moden wrote:

I also suspect that you want this to be conditional to check if there already is a "First Try" or not and add the extra row if there isn't. I also suspect that there is more than one TKey in that same table that would also need the same conditional treatment.
Is that correct?

--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)

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 7

>> Joe, do you convert back to numeric to get your next successive order number and then convert back to character data? With all the back and forth about STORING order numbers, what about generating them? At some point, we have to determine what order number to assign to a newly created order right? <<

Consecutive invoice numbers are required by law in Italy and maybe some other countries. The bad news about consecutive orders on documents as it lets criminals predict the next valid encoding for forgery. These days I would prefer to use the CREATE SEQUENCE statement inside a stored procedure. This would give me strings which sort consecutively, and let me add the check digits, any required punctuation, or prefixes.. There's not a lot of back and forth when you create an invoice number one and only one time in one and only one way and only one in one place in your schema.

I can also allocate blocks of invoice numbers with that statement and then use them one at a time.

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

jcelko212 32090 wrote:

>> Joe, do you convert back to numeric to get your next successive order number and then convert back to character data? With all the back and forth about STORING order numbers, what about generating them? At some point, we have to determine what order number to assign to a newly created order right? <<
Consecutive invoice numbers are required by law in Italy and maybe some other countries. The bad news about consecutive orders on documents as it lets criminals predict the next valid encoding for forgery. These days I would prefer to use the CREATE SEQUENCE statement inside a stored procedure. This would give me strings which sort consecutively, and let me add the check digits, any required punctuation, or prefixes.. There's not a lot of back and forth when you create an invoice number one and only one time in one and only one way and only one in one place in your schema.
I can also allocate blocks of invoice numbers with that statement and then use them one at a time.

Interesting. Are you suggesting that the CREATE SEQUENCE statement itself would add the check digits, any required punctuation, or prefixes? I ask because I don't know of any instances of CREATE SEQUENCE that do those things nor why you might think that the same thing cannot not done with a column that carries the IDENTITY property or why you may think that it would require "a lot of back and forth" or why you may think that it could not be done "only one way and only one in one place in your schema".

I DO, however, agree that producing consecutive invoice numbers by customer would be a real pain for things that used an IDENTITY column.

--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)

x SSC-Insane Points: 23660 More actions · Answer 9

"or why you may think that it would require "a lot of back and forth" or why you may think that it could not be done "only one way and only one in one place in your schema"."

I think he was quoting me on the back and forth 🙂

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 10

>> Are you suggesting that the CREATE SEQUENCE statement itself would add the check digits, any required punctuation, or prefixes? <<

No, it have to add code in that sort procedure to compute the check digit, prefixes and punctuation. Somewhere in my library. I have an entire book on various check digit schemes, so there would be no reasonable way to specify a particular kind of check digits as part of the standard.

>> .. why you might think that the same thing cannot not done with a column that carries the IDENTITY property or why you may think that it would require "a lot of back and forth" or why you may think that it could not be done "only one way and only one in one place in your schema" <<

If I have a "CREATE PROCEDURE New_Invoice", I can also create a "CREATE PROCEDURE New_Purchase Order", etc. All of which use a different "CREATE SEQUENCE" statement internally. Each of these can have a different check digit or different format. In fact, I had one client who used a mod function for some of their paperwork (i.e. all the purchase orders had a multiple of three as part of their identifier)

Assuming that I keep these procedures simple, then the code should port. Also one of the options with a sequence is that you can get blocks of sequence numbers, instead of one at a time like you would get with the identity property. This would let me issue blocks of "blank" forms, just as we do now with paper forms.

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

Thanks, Joe. I appreciate the feedback. I guess my next question would be rhetorical but I'm thinking it... When was the last time you actually had to port a database? 😀 My first and only "true port" was way back in 2002 (Oracle to SQLServer). Virtually nothing ported without requiring some modification but it took only a couple of days to ferret all of that out and fix it. The worst part was that someone had used CONNECT BY on the Oracle side and we had to write (it was SQL Server 2000) a proc with a Temp Table and While loop to replace the functionality.

The second worst part was this funny thing (which was Standard in Oracle) called "Sub-Query Refactoring" that we had to rewrite. In case you've forgotten, that's what we call "CTEs" in SQL Server.

Heh... just imagine trying to convince some Oracle rockstars that they shouldn't use proprietary or other non-standard code because they might someday have to port their code especially for such incredibly useful functionality that was also much easier to write and use. 😉

On your CREATE PROCEDURE New_* procedures, I agree that's one of the good uses for sequences but it's also a good use for columns that have the IDENTITY property. And, yes, you can "reserve" the next set of "N" numbers by simply making "blank" entries into the table as place holders. If you don't do the same with Sequences, then you won't actually know why the numbers are missing from the table. As you say, it's just as we do now with paper forms.

Again, though, I totally agree that Sequences are head and shoulders above columns that have the IDENTITY property when it comes to things like having different sequences based on different customers or even the same same sequence used across multiple tables.

--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)

Lindsay Newbie Points: 9 More actions · Answer 12

SQL is the fashionable language to query a database. PL SQL essentially stands for "Procedural Language extensions to SQL." This is the extension of Structured Query Language (SQL) that is used in Oracle. T-SQL essentially stands for " Transact-SQ

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 13

Ask as many questions as you wish, but please follow the forum rules and post DDL try to follow standards and show us what you tried on your own before you asked us to do your homework. After 30 years of working with SQL and on forums, I just expect minimal polite behavior from people that post. And I do not suffer, rude, lazy people easily.

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

jcelko212 32090 wrote:

I just expect minimal polite behavior from people that post. And I do not suffer, rude, lazy people easily.

To be honest, it's ironic that you would post such a thing.

--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)