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 ««12345»»»

Careful with table name alias Expand / Collapse
Author
Message
Posted Friday, May 14, 2010 3:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 12,962, Visits: 10,733
saranya.megalai (5/14/2010)
if flag is alias then can we use next statement like

select [flag='y'].some_data from [where]?


No

I haven't tested it, but the following should work:

select [flag='y'].some_data from [where] [flag='y']





How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #921852
Posted Friday, May 14, 2010 4:27 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 22, 2013 1:05 AM
Points: 248, Visits: 373
Really interesting and good question.

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
Post #921893
Posted Friday, May 14, 2010 4:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
Really its a good question about square brackets.we can use square brackets efficiently at any time.

Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #921908
Posted Friday, May 14, 2010 5:04 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, June 27, 2014 2:20 AM
Points: 969, Visits: 691
Interesting question, it was the alias that almost caught me out. Have seen plenty of attributes named as reserved words in the past which has caused a few issues unless squared brackets were used. Thinking back I had an interview question once where there was a table called ORDER and the question was what would happen.

Hope this helps,
Rich



Post #921911
Posted Friday, May 14, 2010 6:04 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 1, 2013 2:25 PM
Points: 428, Visits: 627
saranya.megalai (5/14/2010)
if flag is alias then can we use next statement like

select [flag='y'].some_data from [where]?


Yes it would look like:
Select [flag ='y' ].some_data from [where] [flag ='y' ]
Post #921938
Posted Friday, May 14, 2010 6:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 16, 2011 1:25 AM
Points: 10, Visits: 174
Creative statement good for braintraining but with a high theoretical fineness.
Not any SQL developer with some common sense would name a table [where] nor would invent the alias [flag ='y' ]



Post #921979
Posted Friday, May 14, 2010 6:50 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 2:36 PM
Points: 136, Visits: 324
There is nothing special about flag='y'... it's text. It could be anything He's just aliasing the table.

The point is that by using square brackets you can alias something to even something crazy that looks like an expression.


--These are all the same...

Select * from [where] [flag ='y' ]

Select * from [where] [Beer]

Select [flag ='y' ].* from [where] [flag ='y' ]

Select [Beer].* from [where] [Beer]

Select table1.some_data from [where] table1


Post #921983
Posted Friday, May 14, 2010 7:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:35 AM
Points: 2,817, Visits: 2,563
When I actually realized that
[flag ='y' ]
was an alias....I actually laughed. This is a situation so unlikely that it is humorous, but it teaches a good lesson. Great question for a Friday. Thanks.
Post #921998
Posted Friday, May 14, 2010 7:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 22, 2014 4:56 AM
Points: 1,093, Visits: 2,617
Nice one.... also good to use as an interview question



_______________________________________________________________________
For better assistance in answering your questions, click here
Post #922024
Posted Friday, May 14, 2010 7:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 12:37 PM
Points: 2,391, Visits: 17,929
I love it! This ranks right up there with the Middle Join QOD, which is one of my all time favorites. I think someone actually tried to write a SQL Statement once that read as a poem using carefully named tables and a ton of table and column aliases, but I don't remember where I saw that now.

This is great, nicely done Roshan!
Chad
Post #922031
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse