Solved

What's going on with this query?

Posted on 2014-02-05
2
280 Views
Last Modified: 2014-02-05
I'm looking at a query and it utilizes some shorthand that I'm not familiar with and I wanted to run it past the ninjas so I could understand it better.

Here's the select statement:

select z.capxid, z.spmodelid
      , z.assettypeid, at.shortname assettypename, at.refname assettyperefname
      , z.description, z.purchasemonth, z.priorityflag
      , z.equipmenttypeid, et.shortname equipmenttypename, et.refname equipmenttyperefname
      , z.financingflag, z.financingcomment
      , z.financingoptionid, fo.shortname financingoptionname, fo.refname financingoptionrefname
      , z.neworreplace, z.revenueornon, z.itemcost, z.shipping, z.installation, z.taxes
      , z.totalcost, z.explanation, z.codesrequirementflag, z.codescomment
      , z.additionalnewcasesflag, z.additionalnewcases, z.additionalnewamount, z.additionalnewcomment
      , z.projectedlosscasesflag, z.projectedlosscases, z.projectedlossamount, z.projectedlosscomment
      , z.budgetnewcasesflag, z.budgetnewcases, z.budgetnewamount, z.budgetnewcomment
      , z.updateuserid, u.displayname updateusername
      , dbo.ptl_formatdate(z.updatedate,'DATETIME') updatedttm
      from strpl_CapX z
            inner join strpl_Model m on m.spmodelid = z.spmodelid
            inner join fin_AssetType at on at.assettypeid = z.assettypeid
            left outer join mat_EquipmentType et on et.equipmenttypeid = z.equipmenttypeid
            left outer join fin_FinancingOption fo on fo.financingoptionid = z.financingoptionid
            left outer join ptl_user u on z.updateuserid = u.userid
      where z.spmodelid = @spmodelid
      order by at.sortorder, at.shortname, z.purchasemonth, z.description

I'm hip to the strpl_CapX table, but I've never used the "z" dynamic that is here and I wanted to make sure that what appears to be the case, is indeed what's happening.

It looks as though that rather than have to write out "strpl_CapX.capxid," I can instead writer "z.capxid" provided I put a "z" after the name of the table like what you see at the beginning of the FROM clause: from strpl_CapX z. Is that right? The above query works so I'm not questioning its utility, I'm just trying to figure out the scripting conventions. The joins are a subject all by themselves, but I wanted to first understand the apparent one letter abbreviations for the tables themselves and ask if that is, in fact, what they are.
0
Comment
Question by:brucegust
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 11

Accepted Solution

by:
Simone B earned 250 total points
ID: 39836943
Yes, that's exactly the case. Not only does it save you time in typing, but there are other advantages.

For example, if you ever need to self-join a table, you give each a different alias in the FROM clause, for example z and x. Then in your SELECT, using either x or z will select from the correct instance of the table.
0
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 250 total points
ID: 39836944
Yes z is an alias for the strl_CapX table as defined in the from clause

http://sqlmag.com/t-sql/aliases-t-sql
0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question