What's going on with this query?
Posted on 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.