SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL Parsing Crazy Eights


T-SQL Parsing Crazy Eights

Author
Message
David Walker-278941
David Walker-278941
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 231
Tao Klerks (9/23/2009)
David Walker-278941 (9/23/2009)
Yes, but I'm not forgiving of superfluous apostrophes in the word "its". The sentence gets a Fail. "It's" means "it is" and nothing else.

Ah but if you're going to be all finicky about it, "it's" can actually mean "it is" or "it has"...


You're right about that, and I missed it. It's been so long since I used "it's" to mean anything other than "it is" that I forgot about that. :-)
john.arnott
john.arnott
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2024 Visits: 3059
David Walker-278941 (9/23/2009)
Tao Klerks (9/23/2009)
David Walker-278941 (9/23/2009)
Yes, but I'm not forgiving of superfluous apostrophes in the word "its". The sentence gets a Fail. "It's" means "it is" and nothing else.

Ah but if you're going to be all finicky about it, "it's" can actually mean "it is" or "it has"...


You're right about that, and I missed it. It's been so long since I used "it's" to mean anything other than "it is" that I forgot about that. :-)

Hmmmm. I hadn't noticed that error the other day when the QOD was published. I didn't retain a copy of the original text submitted a couple of months ago, so now I'm curious as to whether its extraneous apostophe is my error or it's something that was introduced by SSC in the editing process. I hope that last sentence demonstrates that I do understand the difference between "it's" and "its".

Meanwhile, was the parsing exercise valuable to you? Did you learn something?
David Walker-278941
David Walker-278941
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 231
john.arnott (9/23/2009)
David Walker-278941 (9/23/2009)
Tao Klerks (9/23/2009)
David Walker-278941 (9/23/2009)
Yes, but I'm not forgiving of superfluous apostrophes in the word "its". The sentence gets a Fail. "It's" means "it is" and nothing else.

Ah but if you're going to be all finicky about it, "it's" can actually mean "it is" or "it has"...


You're right about that, and I missed it. It's been so long since I used "it's" to mean anything other than "it is" that I forgot about that. :-)

Hmmmm. I hadn't noticed that error the other day when the QOD was published. I didn't retain a copy of the original text submitted a couple of months ago, so now I'm curious as to whether its extraneous apostophe is my error or it's something that was introduced by SSC in the editing process. I hope that last sentence demonstrates that I do understand the difference between "it's" and "its".

Meanwhile, was the parsing exercise valuable to you? Did you learn something?


To be honest, I didn't learn much that was useful. If there are no blanks between values and their aliases, that's pretty bad coding. And unfortunately for me, when I read bad grammar, it interrupts the flow and breaks my concentration. But that's something I have to work on.

I would rather start a campaign against unnecessary table aliases, especially in Join statements, than spend time on edge cases in the parser like this. Is it written in the SQL specification that blanks are not required between a value and a column alias, or is it just something that someone observed in one release and in one implementation? If it's not part of the spec, then learning it is not very useful.

For table alises, programmers seem to get the idea that they are a required part of the syntax. In fact, for many readers, having to read someone else's code with Join statements that are full of table aliases requires an extra level of concentration. Table aliases are *sometimes* required, but not all of the time. They hinder human readability. Trying to unlearn bad behavior (unneccessary table aliases) is more important than worrying about oddball syntax parsing. :-)
ssismaddy
ssismaddy
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1090 Visits: 2386
It might be the silly question....but, why we are not able to display e ??

select 1.a -- gives the column name a and prints 1 , which is fine.
select 1.e -- gives no column name !!!!!!!!!

howz this?
David Walker-278941
David Walker-278941
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 231
ssismaddy (9/23/2009)
It might be the silly question....but, why we are not able to display e ??

select 1.a -- gives the column name a and prints 1 , which is fine.
select 1.e -- gives no column name !!!!!!!!!

howz this?


1.e is probably the same as 1.0e0, which is scientific notation. So, the "e" is part of the number, just like the decimal point, and is not interpreted as a column alias. That's my guess.
VM-723206
VM-723206
Right there with Babe
Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)

Group: General Forum Members
Points: 778 Visits: 267
Nice one!
mohd.nizamuddin
mohd.nizamuddin
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 198
David Walker-278941 (9/23/2009)
ssismaddy (9/23/2009)
It might be the silly question....but, why we are not able to display e ??

select 1.a -- gives the column name a and prints 1 , which is fine.
select 1.e -- gives no column name !!!!!!!!!

howz this?


1.e is probably the same as 1.0e0, which is scientific notation. So, the "e" is part of the number, just like the decimal point, and is not interpreted as a column alias. That's my guess.

Yes David you are absolutely correct.
mohd.nizamuddin
mohd.nizamuddin
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 198
David Walker-278941 (9/23/2009)
john.arnott (9/23/2009)
David Walker-278941 (9/23/2009)
Tao Klerks (9/23/2009)
David Walker-278941 (9/23/2009)
Yes, but I'm not forgiving of superfluous apostrophes in the word "its". The sentence gets a Fail. "It's" means "it is" and nothing else.

Ah but if you're going to be all finicky about it, "it's" can actually mean "it is" or "it has"...


You're right about that, and I missed it. It's been so long since I used "it's" to mean anything other than "it is" that I forgot about that. :-)

Hmmmm. I hadn't noticed that error the other day when the QOD was published. I didn't retain a copy of the original text submitted a couple of months ago, so now I'm curious as to whether its extraneous apostophe is my error or it's something that was introduced by SSC in the editing process. I hope that last sentence demonstrates that I do understand the difference between "it's" and "its".

Meanwhile, was the parsing exercise valuable to you? Did you learn something?


To be honest, I didn't learn much that was useful. If there are no blanks between values and their aliases, that's pretty bad coding. And unfortunately for me, when I read bad grammar, it interrupts the flow and breaks my concentration. But that's something I have to work on.

I would rather start a campaign against unnecessary table aliases, especially in Join statements, than spend time on edge cases in the parser like this. Is it written in the SQL specification that blanks are not required between a value and a column alias, or is it just something that someone observed in one release and in one implementation? If it's not part of the spec, then learning it is not very useful.

For table alises, programmers seem to get the idea that they are a required part of the syntax. In fact, for many readers, having to read someone else's code with Join statements that are full of table aliases requires an extra level of concentration. Table aliases are *sometimes* required, but not all of the time. They hinder human readability. Trying to unlearn bad behavior (unneccessary table aliases) is more important than worrying about oddball syntax parsing. :-)

I agree with David regarding unnecessary table aliases.
Just on it, that we should alias a table when a table has been used multiple times in a single SQL (considering sub-query also).
Tao Klerks
Tao Klerks
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1543 Visits: 1249
mohd.nizamuddin (9/30/2009)
David Walker-278941 (9/23/2009)
For table alises, programmers seem to get the idea that they are a required part of the syntax. In fact, for many readers, having to read someone else's code with Join statements that are full of table aliases requires an extra level of concentration. Table aliases are *sometimes* required, but not all of the time. They hinder human readability. Trying to unlearn bad behavior (unneccessary table aliases) is more important than worrying about oddball syntax parsing. :-)

I agree with David regarding unnecessary table aliases.
Just on it, that we should alias a table when a table has been used multiple times in a single SQL (considering sub-query also).

I don't really see how this discussion is related (T-SQL engine eccentricities when parsing numbers and their column aliases vs. naming conventions on table aliases), but I disagree that extensive use of table aliases consistently hinders readability... It enables the SQL to be more compact, which can be a boon when table names are long, eg (query entirely made up to protect the innocent):


SELECT
SOL.OrderNumber,
SOL.LineNumber,
SOH.OrderTotal,
SOP.PaymentAmount
FROM dbo.StandingOrderLine SOL
INNER JOIN dbo.StandingOrderHeader SOH ON SOL.CompanyID = SOH.CompanyID
AND SOL.StandingOrderID = SOH.StandingOrderID
INNER JOIN dbo.StandingOrderPayment SOP ON SOH.CompanyID = SOP.CompanyID
AND SOH.StandingOrderID = SOP.StandingOrderID
WHERE SOL.LineStatus = 'Shipped'
AND SOP.PaymentStatus = 'Declined'



vs


SELECT
dbo.StandingOrderLine.OrderNumber,
dbo.StandingOrderLine.LineNumber,
dbo.StandingOrderHeader.OrderTotal,
dbo.StandingOrderPayment.PaymentAmount
FROM dbo.StandingOrderLine
INNER JOIN dbo.StandingOrderHeader ON dbo.StandingOrderLine.CompanyID = dbo.StandingOrderHeader.CompanyID
AND dbo.StandingOrderLine.StandingOrderID = dbo.StandingOrderHeader.StandingOrderID
INNER JOIN dbo.StandingOrderPayment ON dbo.StandingOrderHeader.CompanyID = dbo.StandingOrderPayment.CompanyID
AND dbo.StandingOrderHeader.StandingOrderID = dbo.StandingOrderPayment.StandingOrderID
WHERE dbo.StandingOrderLine.LineStatus = 'Shipped'
AND dbo.StandingOrderPayment.PaymentStatus = 'Declined'




The first time you read the aliased query it may take a few more seconds to parse, but assuming that the schema is familiar to those reading and writing the queries and these objects (and their usual aliases) are seen with some frequency, this rapidly becomes a way to write and read queries more quickly, without cryptic table names or synonyms.

It also helps avoid long table names and column names running off the edge of the page/screen, which hinders readability and efficiency much more!

So no, table aliases are not a required part of the syntax, and yes, maybe some developers abuse them, but "unnecessary" does not necessarily imply "undesirable"...

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1277 Visits: 1499
I agree with Tao that thoughtful aliasing, as opposed to A. B, C, will make code easier to read for the programmer, debugger, or DBA. Most who have been around a little while won't have to wonder what OH and OD reference. OH.customer_name doesn't present nearly the challenge to read as does SalesOrderHeader.customer_name.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search