On 2015-03-16 06:16 PM, Drago, William @ CSG - NARDA-MITEQ wrote:
> All,
>
> Some of my Select statements are pretty long and I'm starting to think it'd
> be a good idea to always include table names of columns instead of just when
> they are not unique. This would make the Select statements longer, but
> perhaps easier to understand if the reader knows where each column is from.
>
> Any thoughts on this? I realize something like this can be highly subjective,
> but I'm wondering if there's a generally accepted practice in the SQLite
> world.
I won't try to imagine this applies to the SQLite or any other SQL
world, but I've had good success (In terms of always understanding
queries that needs to be referred back to after some months and
legibility to others plus zero SQL engine confusion or incorrect
queries) when using very short aliasing as a standard.
I've started the practice out of necessity using MS SQL where automated
queries by default append the table-name to every column which made it
almost illegible and fills up the screen with nonsense text making the
query hard to read from among the riffraff. (People using MS Query via
Excel might be familiar with this example).
A typical Query offered by some designer might start out like this:
SELECT PorMasterHeader.POrder, PorMasterHeader..EntryDate,
PorMasterHeader.CLientCode, AprClients.ClientName, PorMaster.Lines,
PorMasterDetail.LineNo, PorMasterDetail.StockCode,
PorMasterDetail.Price, PorMasterDetail.OrderQty,
(PorMasterDetail.OrderQty*PorMasterDetail.Price) AS TotCost
FROM [SAPSystemCompanyAX].[dbo].[PorMasterHeader] PorMasterHeader,
[SAPSystemCompanyAX].[dbo].[PorMaster] PorMaster,
[SAPSystemCompanyAX].[dbo].[PorMasterDetail] PorMasterDetail,
[SAPSystemCompanyAX].[dbo].[AprClients] AprClients
WHERE PorMasterDetail.POrder=PorMasterHeader.POrder AND
PorMaster.POrder=PorMasterHeader.POrder AND
PorMasterDetail.LineNo>1AND AprClients.ClientCode=PorMasterHeader.Client
ORDER BY AprClients.ClientName, PorMasterHeader.POrder,
PorMasterDetail.LineNo
which is a wall-of-text mess...
Making use of nothing more than short clear aliases and better
indentation fixes it to read like this:
SELECT PH.POrder, PH..EntryDate, PH.CLientCode, CL.ClientName,
PM.Lines, PD.LineNo,
PD.StockCode, PD.Price, PD.OrderQty, (PD.OrderQty*PD.Price)
AS TotCost
FROM [SAPSystemCompanyAX].[dbo].[PorMasterHeader] PH,
[SAPSystemCompanyAX].[dbo].[PorMaster] PM,
[SAPSystemCompanyAX].[dbo].[PorMasterDetail] PD,
[SAPSystemCompanyAX].[dbo].[AprClients] CL
WHERE PD.POrder=PH.POrder AND PM.POrder=PH.POrder AND PD.LineNo>1
AND CL.ClientCode=PH.Client
ORDER BY CL.ClientName, PM.POrder, PD.LineNo
which is legible to all, works always exactly (no column name is left to
ambiguity) and easy to understand and is DB-Engine independent.
The best part being, the more convoluted the query, the larger the
space-saving.
Note: It also helps to have a bit of convention in place, such as ALWAYS
aliasing [AprClients] to CL which will make you "see" the meaning of a
query faster over time - but be careful, it may happen that someone else
aliases some other table to CL and if you don't check it, might end up
spending hours chasing obscure bugs.
Good luck,
Ryan
