November 12, 2018 at 10:00 am
michael.leach2015 - Sunday, November 11, 2018 6:01 PMJeff Moden - Sunday, November 11, 2018 1:59 PMANSI/ISO standards have absolutely nothing to do with how to post a question.This is what was confusing me. I'm sure standards apply to writing code, but I don't see how to apply that to posting a question. As far as applying those standards to writing code, what does that exactly involve?
If you have a choice of using an ANSI ISO standard construct that Microsoft is now supporting, then write code with it. Whenever possible, avoid dialect; this means something Microsoft added later, but it usually means leftovers from the original Sybase SQL Server. For example, prefer the standard CAST() over the old Sybase convert(); use COALESCE () instead of the old Sybase is null (); use the standard table construction syntax instead of the old Sybase SELECT..UNION ALL; etc.
Please post DDL and follow ANSI/ISO standards when asking for help.
November 12, 2018 at 10:01 am
michael.leach2015 - Sunday, November 11, 2018 6:01 PMJeff Moden - Sunday, November 11, 2018 1:59 PMANSI/ISO standards have absolutely nothing to do with how to post a question.This is what was confusing me. I'm sure standards apply to writing code, but I don't see how to apply that to posting a question. As far as applying those standards to writing code, what does that exactly involve? Does that mean writing statements and clauses on the same line or separate lines? Does also refer to indenting with a certain number of spaces? What exactly does this involve? So far I have not read anything that said to use ANSI/ISO standards when writing SQL code. Instead, the videos I watch just show what to do, and then I do it.
Jeff is addressing one of Joe's points here. ANSI Sql basically means that the code that I write like on sql server or any system , I write in a way so that I can copy and paste it into oracle or any ansi compliant system and it will work as is. Of course Sql server and oracle have functionality beyond the ansi compliant portion of the code. The message is that it is this extra , though non ansi features of sql are powerful and that porting from one system to another is rare. Conclusion? Dont worry about making your code ansi compliant.
Do a bit of internet reading and maybe a good book on SQL as a language. And no this is not about how you format your code (indenting , etc.. etc...) Common sense is all you need to guide you to know how to make your code readable to you and to others. What we mean instead is, for instance Select , create table, create view ... are ansi compliant functions. They are coded the same across systems. Others are specific to sql server like the sp_describe_first_result_set I mentioned in an earlier post. So in essence , t-sql is like a spectrum from point a to point b representing 100% of available functions, operators, clauses. Up to a certain point within that range say like 70% (just making this up) will be ansi compliant. The rest of the funcitons, operators, etc.. are specific to sql server and will only work this system. So to Jeff's point again , you dont have to limit yourself to only that 70% of functionality witihn sql server. Hope this makes sense.
----------------------------------------------------
November 12, 2018 at 2:06 pm
jcelko212 32090 - Monday, November 12, 2018 10:00 AMmichael.leach2015 - Sunday, November 11, 2018 6:01 PMJeff Moden - Sunday, November 11, 2018 1:59 PMANSI/ISO standards have absolutely nothing to do with how to post a question.This is what was confusing me. I'm sure standards apply to writing code, but I don't see how to apply that to posting a question. As far as applying those standards to writing code, what does that exactly involve?
If you have a choice of using an ANSI ISO standard construct that Microsoft is now supporting, then write code with it. Whenever possible, avoid dialect; this means something Microsoft added later, but it usually means leftovers from the original Sybase SQL Server. For example, prefer the standard CAST() over the old Sybase convert(); use COALESCE () instead of the old Sybase is null (); use the standard table construction syntax instead of the old Sybase SELECT..UNION ALL; etc.
CAST is just a re-branded CONVERT minus the ability to use the style parameter.
COALESCE and ISNULL aren't equivalent and can produce very different results.... Itzig has an entire article covering the differences... https://www.itprotoday.com/sql-server/coalesce-vs-isnull
The "table value constructor" is limited to inserting 1000 rows per execution. The UNION ALL syntax does not have that limitation.
November 12, 2018 at 4:47 pm
MMartin1 - Monday, November 12, 2018 10:01 AMmichael.leach2015 - Sunday, November 11, 2018 6:01 PMJeff Moden - Sunday, November 11, 2018 1:59 PMANSI/ISO standards have absolutely nothing to do with how to post a question.This is what was confusing me. I'm sure standards apply to writing code, but I don't see how to apply that to posting a question. As far as applying those standards to writing code, what does that exactly involve? Does that mean writing statements and clauses on the same line or separate lines? Does also refer to indenting with a certain number of spaces? What exactly does this involve? So far I have not read anything that said to use ANSI/ISO standards when writing SQL code. Instead, the videos I watch just show what to do, and then I do it.
Jeff is addressing one of Joe's points here. ANSI Sql basically means that the code that I write like on sql server or any system , I write in a way so that I can copy and paste it into oracle or any ansi compliant system and it will work as is. Of course Sql server and oracle have functionality beyond the ansi compliant portion of the code. The message is that it is this extra , though non ansi features of sql are powerful and that porting from one system to another is rare. Conclusion? Dont worry about making your code ansi compliant.
Do a bit of internet reading and maybe a good book on SQL as a language. And no this is not about how you format your code (indenting , etc.. etc...) Common sense is all you need to guide you to know how to make your code readable to you and to others. What we mean instead is, for instance Select , create table, create view ... are ansi compliant functions. They are coded the same across systems. Others are specific to sql server like the sp_describe_first_result_set I mentioned in an earlier post. So in essence , t-sql is like a spectrum from point a to point b representing 100% of available functions, operators, clauses. Up to a certain point within that range say like 70% (just making this up) will be ansi compliant. The rest of the funcitons, operators, etc.. are specific to sql server and will only work this system. So to Jeff's point again , you dont have to limit yourself to only that 70% of functionality witihn sql server. Hope this makes sense.
This helps. I see what you mean now. Thank you for your reply.
November 12, 2018 at 5:04 pm
The legacy Sybase functions reflect a COBOL mentality, and not an SQL or RDBMS model the world. You mentioned that the CONVERT() allows formatting; but what's the rule about doing display formatting in the database in RDBMS? You don't do it. And that's why you have a presentation layer. I've always loved seeing people having to unconvert dates because the office in the UK and the office in the US and the office in the EU decided to use this "feature" to put their data in a local dialect. The definition of a CAST() comes out of the standards and behaves the same way (well in theory), no matter which SQL engine it's in.
COBOL has a PICTURE clause for the fields in its records. This clause, as the punctuation, and other display edits because COBOL has only one tier. Nobody thought about client/server back then. This is the reason the Sybase MONEY types behave the way they do; don't scare the COBOL programmers
COALESCE () Can take a list of expressions and it will return a result with highest the data type, while the old ISNULL() is limited to parameters and it returns the data type of the first one.
The SELECT… UNION ALL is just awful and bulky. It also perverts how the SELECT..FROM statement is supposed to work in SQL. I probably would never insert 1000 rows in a single insertion statement; there much better tools for large amounts of data.
One of my favorite examples of a getting caught on proprietary dialect versus standard SQL was the old BIT data type. It was originally defined as {0,1} just as computer science type would think. A little BIT later Microsoft changed their mind and it became a numeric data type. All numeric datatypes have to take a NULL. Somebody who didn't put an explicit NOT NULL constraint on the old BIT columns got some really nice surprises. This point out another little problem when you write with proprietary syntax all the proprietors are perfectly free to change its definition from one release to the next. They do no have to match anyone else! When you write with ANSI/ISO standards, is an awful lot harder to change things and when they are changed, it shows up in the trade press.
Please post DDL and follow ANSI/ISO standards when asking for help.
November 12, 2018 at 10:25 pm
jcelko212 32090 - Monday, November 12, 2018 5:04 PMThe legacy Sybase functions reflect a COBOL mentality, and not an SQL or RDBMS model the world. You mentioned that the CONVERT() allows formatting; but what's the rule about doing display formatting in the database in RDBMS? You don't do it. And that's why you have a presentation layer. I've always loved seeing people having to unconvert dates because the office in the UK and the office in the US and the office in the EU decided to use this "feature" to put their data in a local dialect. The definition of a CAST() comes out of the standards and behaves the same way (well in theory), no matter which SQL engine it's in.COBOL has a PICTURE clause for the fields in its records. This clause, as the punctuation, and other display edits because COBOL has only one tier. Nobody thought about client/server back then. This is the reason the Sybase MONEY types behave the way they do; don't scare the COBOL programmers
COALESCE () Can take a list of expressions and it will return a result with highest the data type, while the old ISNULL() is limited to parameters and it returns the data type of the first one.
The SELECT… UNION ALL is just awful and bulky. It also perverts how the SELECT..FROM statement is supposed to work in SQL. I probably would never insert 1000 rows in a single insertion statement; there much better tools for large amounts of data.
One of my favorite examples of a getting caught on proprietary dialect versus standard SQL was the old BIT data type. It was originally defined as {0,1} just as computer science type would think. A little BIT later Microsoft changed their mind and it became a numeric data type. All numeric datatypes have to take a NULL. Somebody who didn't put an explicit NOT NULL constraint on the old BIT columns got some really nice surprises. This point out another little problem when you write with proprietary syntax all the proprietors are perfectly free to change its definition from one release to the next. They do no have to match anyone else! When you write with ANSI/ISO standards, is an awful lot harder to change things and when they are changed, it shows up in the trade press.
That is enough COBOL Programmer bashing!!!!!! Get over it, Joe. COBOL programmers are not EVIL. Many of us have moved on to working quite well with SQL Server without having to reinvent COBOL methods in our code.
Also, sometimes you do need to format in SQL as the output of the query is being directly written to text files.
Now, your horse needs a good brushing and put out to pasture for a while.
November 13, 2018 at 6:34 am
jcelko212 32090 - Monday, November 12, 2018 5:04 PMThe legacy Sybase functions reflect a COBOL mentality, and not an SQL or RDBMS model the world. You mentioned that the CONVERT() allows formatting; but what's the rule about doing display formatting in the database in RDBMS? You don't do it. And that's why you have a presentation layer. I've always loved seeing people having to unconvert dates because the office in the UK and the office in the US and the office in the EU decided to use this "feature" to put their data in a local dialect. The definition of a CAST() comes out of the standards and behaves the same way (well in theory), no matter which SQL engine it's in.COBOL has a PICTURE clause for the fields in its records. This clause, as the punctuation, and other display edits because COBOL has only one tier. Nobody thought about client/server back then. This is the reason the Sybase MONEY types behave the way they do; don't scare the COBOL programmers
COALESCE () Can take a list of expressions and it will return a result with highest the data type, while the old ISNULL() is limited to parameters and it returns the data type of the first one.
The SELECT… UNION ALL is just awful and bulky. It also perverts how the SELECT..FROM statement is supposed to work in SQL. I probably would never insert 1000 rows in a single insertion statement; there much better tools for large amounts of data.
One of my favorite examples of a getting caught on proprietary dialect versus standard SQL was the old BIT data type. It was originally defined as {0,1} just as computer science type would think. A little BIT later Microsoft changed their mind and it became a numeric data type. All numeric datatypes have to take a NULL. Somebody who didn't put an explicit NOT NULL constraint on the old BIT columns got some really nice surprises. This point out another little problem when you write with proprietary syntax all the proprietors are perfectly free to change its definition from one release to the next. They do no have to match anyone else! When you write with ANSI/ISO standards, is an awful lot harder to change things and when they are changed, it shows up in the trade press.
Heh... actually, with an almost forced "Top Down" and "Divide and Conquer" methodology, COBOL was ahead of its time for processing large batches of data. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2018 at 8:32 am
michael.leach2015 - Tuesday, November 6, 2018 6:57 PMSome of our systems were created by a long-time employee who knew everything about it, but didn't write much of it down. When he was downsized out, the "documentation" exited the building within his brain.
Let me buy you a virtual beer, or better yet a virtual scotch, and let's talk about legacy systems written by morons or truly evil people. 🙁
Decades ago, I worked at a state agency or one of the COBOL programmers named his paragraphs on whatever theme was in his head at the time. I had to try and figure out his "countries of the world" payroll (GOTO Afghanistan. PERFORM Mongolia, etc.) and then found out that another one of his programs was written while he was gardening (GOTO chrysanthemum.)
It wasn't just that his choice of names was totally unrelated to the data elements, but he wrote what we used to call "spaghetti code"; this refers to languages that have GOTO's and other control flow constructs. This is a reference to twisting and jumping all over the place, as you have no idea what the pre-and post-conditions of any statement in the program was. This was why we went into structured programming and decided that code module should have one entry point and one exit point, that we ought to implement things with if-then-else and loops, and all the stuff the current programmers take for granted.
Please post DDL and follow ANSI/ISO standards when asking for help.
November 13, 2018 at 8:41 am
jcelko212 32090 - Thursday, November 8, 2018 3:27 PMmichael.leach2015 - Tuesday, November 6, 2018 6:57 PMSome of our systems were created by a long-time employee who knew everything about it, but didn't write much of it down. When he was downsized out, the "documentation" exited the building within his brain.
Let me buy you a virtual beer, or better yet a virtual scotch, and let's talk about legacy systems written by morons or truly evil people. 🙁
Decades ago, I worked at a state agency or one of the COBOL programmers named his paragraphs on whatever theme was in his head at the time. I had to try and figure out his "countries of the world" payroll (GOTO Afghanistan. PERFORM Mongolia, etc.) and then found out that another one of his programs was written while he was gardening (GOTO chrysanthemum.)
It wasn't just that his choice of names was totally unrelated to the data elements, but he wrote what we used to call "spaghetti code"; this refers to languages that have GOTO's and other control flow constructs. This is a reference to twisting and jumping all over the place, as you have no idea what the pre-and post-conditions of any statement in the program was. This was why we went into structured programming and decided that code module should have one entry point and one exit point, that we ought to implement things with if-then-else and loops, and all the stuff the current programmers take for granted.
I've lived through all that too, Joe. You don't have to give me a history lesson. I programmed in FORTRAN for a good portion of my career. We were thrilled when we got ISAM files. I learned COBOL, but never had to program in it professionally.
There was one legacy program from a guy who evidently thought he knew how to program in FORTRAN, but he really knew BASIC. All his variables and arrays were named T, TT, T1, T2, A, A1, A2, ... - my coworker found it easier to throw it all out and start over.
November 13, 2018 at 8:58 am
Jeff Moden - Tuesday, November 13, 2018 6:34 AMjcelko212 32090 - Monday, November 12, 2018 5:04 PMHeh... actually, with an almost forced "Top Down" and "Divide and Conquer" methodology, COBOL was ahead of its time for processing large batches of data. 😉
I started life as a Fortran programmer more decades ago than I like to think about (first paying job in 1965) But because it was working for state government, I was asked to do some maintenance on COBOL systems. Hey, you are a programmer, right? State governments are great places to get your first job because the hiring standards are so low. Once the COBOL guys got a year, or sometimes less, they left the state and got a job in the real world. They left their "learning experience" behind for someone else to take care of.
The problem with COBOL as a structured language was it had two major constructs; GOTO and PERFORM. The goto explains itself, but it also implies that there can be no block structure, since you could drop into any part of the program from anywhere (and hence spaghetti code). The perform had to work in that environment and was not a procedure invocation. COBOL works great for large batches of well-understood data, as you said. It's just too specialized is a processing model for anything more complicated. I think I still have a book somewhere in my library on structured COBOL. It's an oldie that goes back to the days when everything had to be structured 🙂
Please post DDL and follow ANSI/ISO standards when asking for help.
November 13, 2018 at 10:02 am
jcelko212 32090 - Tuesday, November 13, 2018 8:58 AMJeff Moden - Tuesday, November 13, 2018 6:34 AMjcelko212 32090 - Monday, November 12, 2018 5:04 PMHeh... actually, with an almost forced "Top Down" and "Divide and Conquer" methodology, COBOL was ahead of its time for processing large batches of data. 😉
I started life as a Fortran programmer more decades ago than I like to think about (first paying job in 1965) But because it was working for state government, I was asked to do some maintenance on COBOL systems. Hey, you are a programmer, right? State governments are great places to get your first job because the hiring standards are so low. Once the COBOL guys got a year, or sometimes less, they left the state and got a job in the real world. They left their "learning experience" behind for someone else to take care of.
The problem with COBOL as a structured language was it had two major constructs; GOTO and PERFORM. The goto explains itself, but it also implies that there can be no block structure, since you could drop into any part of the program from anywhere (and hence spaghetti code). The perform had to work in that environment and was not a procedure invocation. COBOL works great for large batches of well-understood data, as you said. It's just too specialized is a processing model for anything more complicated. I think I still have a book somewhere in my library on structured COBOL. It's an oldie that goes back to the days when everything had to be structured 🙂
You can write structured COBOL code. If you have spaghetti code it is because someone didn't properly design the code in the first place. The real problem is taking over spaghetti code and having to write to the lowest common denominator regarding the code since there isn't enough time to rewrite the old code and still get the new stuff written. Thank God when we wrote new subprograms we were able to write the code correctly and avoid the issue of spaghetti code.
Now, how about coming back to the 21st century and working with SQL.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply