Personally, I'd format it like this to be the most readable:
SELECT PH.POrder as POrder,
PH.EntryDate as EntryDate,
PH.ClientCode as ClientCode,
CL.ClientName as ClientName,
PM.Lines as Lines,
PD.LineNo as LineNo,
PD.StockCode aa StockCode,
PD.Price as Price,
PD.OrderQty as OrderQty,
PD.OrderQty*PD.Price as TotCost
FROM SAPSystemCompanyAX.dbo.PorMasterHeader as PH,
SAPSystemCompanyAX.dbo.PorMaster as PM,
SAPSystemCompanyAX.dbo.PorMasterDetail as PD,
SAPSystemCompanyAX.dbo.AprClients as CL
WHERE PD.POrder = PH.POrder
AND PM.POrder = PH.POrder
AND PD.LineNo > 1
AND CL.ClientCode = PH.Client
ORDER BY ClientName, POrder, LineNo
or, if you prefer the JOIN syntax:
SELECT PH.POrder as POrder,
PH.EntryDate as EntryDate,
PH.ClientCode as ClientCode,
CL.ClientName as ClientName,
PM.Lines as Lines,
PD.LineNo as LineNo,
PD.StockCode aa StockCode,
PD.Price as Price,
PD.OrderQty as OrderQty,
PD.OrderQty*PD.Price as TotCost
FROM SAPSystemCompanyAX.dbo.PorMasterHeader as PH
JOIN SAPSystemCompanyAX.dbo.PorMaster as PM
ON PM.POrder = PH.POrder
JOIN SAPSystemCompanyAX.dbo.PorMasterDetail as PD
ON PD.POrder = PH.POrder
JOIN SAPSystemCompanyAX.dbo.AprClients as CL
ON CL.ClientCode = PH.Client
WHERE PD.LineNo > 1
ORDER BY ClientName, POrder, LineNo
I find gratuitous quoting of symbols somewhat obnoxious and distasteful, as
well as the propensity to (include (extraneous) (brackets) just) for the (sake
(of (adding (brackets)))). I see that your original obviously-ms-generated
query didn't generate its usual crapload of () -- or perhaps you removed them
already :)
---
Theory is when you know everything but nothing works. Practice is when
everything works but no one knows why. Sometimes theory and practice are
combined: nothing works and no one knows why.
>-----Original Message-----
>From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
>bounces at mailinglists.sqlite.org] On Behalf Of R.Smith
>Sent: Monday, 16 March, 2015 11:26
>To: sqlite-users at mailinglists.sqlite.org
>Subject: Re: [sqlite] When to disambiguate column names in queries?
>
>
>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
>_______________________________________________
>sqlite-users mailing list
>sqlite-users at mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
