When all names are disambiguated, simple typos and cut-and-paste errors become less likely to go undetected by the SQL Parser.
See the "No diagnostic given for missing column" thread. -----Urspr?ngliche Nachricht----- Von: Keith Medcalf [mailto:kmedcalf at dessus.com] Gesendet: Dienstag, 17. M?rz 2015 00:53 An: General Discussion of SQLite Database Betreff: Re: [sqlite] When to disambiguate column names in queries? 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 _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
