Bruce Gust
asked on
What's going on with this query?
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.updat edate,'DAT ETIME') 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.
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.updat
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.