Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

T-SQL Parsing Crazy Eights Expand / Collapse
Author
Message
Posted Wednesday, September 23, 2009 8:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 3:54 PM
Points: 67, Visits: 230
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.
Post #792704
Posted Wednesday, September 23, 2009 12:03 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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?
Post #792851
Posted Wednesday, September 23, 2009 12:13 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 3:54 PM
Points: 67, Visits: 230
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.
Post #792854
Posted Wednesday, September 23, 2009 12:37 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:30 PM
Points: 878, Visits: 2,154
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?
Post #792868
Posted Wednesday, September 23, 2009 12:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 3:54 PM
Points: 67, Visits: 230
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.
Post #792878
Posted Friday, September 25, 2009 5:52 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:11 AM
Points: 654, Visits: 267
Nice one!
Post #793750
Posted Wednesday, September 30, 2009 5:19 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 29, 2011 4:14 AM
Points: 318, 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.
Post #795633
Posted Wednesday, September 30, 2009 5:24 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 29, 2011 4:14 AM
Points: 318, 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).
Post #795638
Posted Wednesday, September 30, 2009 5:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:38 PM
Points: 1,385, Visits: 1,249
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.
Post #795653
Posted Wednesday, September 30, 2009 6:46 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #795680
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse