?
Solved

What's going on with this query?

Posted on 2014-02-05
2
Medium Priority
?
301 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
2 Comments
 
LVL 11

Accepted Solution

by:
Simone B earned 1000 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 1000 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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

601 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