RE: Re: [DUG]: Column Names

[Andreas Toth]

Title: Message

Prefixing a table name in front of a table can lead to a lot of unforseen problems down the track especially if the fields are really the same, such as the ID in the example below. My recommendation is not to use this technique.     -Andreas -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Peter Speden Sent: Wednesday, 2 October 2002 11:12 To: Multiple recipients of list delphi Subject: RE: Re: [DUG]: Column Names Neven   Going back to the original SQL you wrote, given that you are doing an inner join on StoreID in the two tables then selecting the two is irrelevant as they will be the same.    So this appears to be just a hypothetical.    Also most of the people I know who design SQL DBs usually prefix a tablename code in front of the field in the db ie in your case we would have s_STOREID and sc_STOREID.  This makes it unique in views as well.  SQL server when designing views where fieldnames are the same will make one of them have an alias (defaults to Expr1), so thinking that it isn’t really a limitation of TDataset in this case.    I can see your point though, but think it is at a hypothetical level.  Perhaps you should write your own TField that supports this functionality.   -----Original Message----- From: Neven MacEwan [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 2 October 2002 10:38 a.m. To: Multiple recipients of list delphi Subject: Re: [DUG]: Column Names   Nello   The design limitation of TDataSet is that 'FieldNames' have to be unique and that 'Origin' is not fully supported, if the definition of a FieldName is not the fully qualified name then it is not necesarilly unique in a joined query, hence I don't think this was considered when the TDataset was conceived, however if you put a TDataset = isam db table then this is fine   Neven   ----- Original Message ----- From: Nello Sestini To: Multiple recipients of list delphi Sent: Wednesday, October 02, 2002 10:26 AM Subject: Re: [DUG]: Column Names   Neven (not Nevan :) )   I still see it as a design limitation of TDataSet (or more precisely TField) since FieldName doesn't   I don't think this has anything to do with TDataset   The issue is what the result set column names are when columns in two joined tables have the same name.   For example the following query in Interbase:       select p.pat_id,a.pat_id     from patient p join patient_alias a on (p.pat_id = a.pat_id)   results in 2 columns "PAT_ID" and "PAT_ID1"   There's no ambiguity here, but if you want more mnemonic names for the result columns you need to alias them in your select   The "name" of the "field" (column) comes from the underlying DBMS not from TDataSet   (I think this was Kyley's original point)   Maybe I am missing YOUR original point <g>       -ns http://www.roserox.co.th   a/ include a Table Qualifier or b/ defer to Origin for Uniqueness (my prefered solution)   in fact the whole naming of TField betrays its origin as SQL Tables do not have fields   Neven (not Nevan)   ----- Original Message ----- From: Kyley Harris To: Multiple recipients of list delphi Sent: Wednesday, October 02, 2002 9:23 AM Subject: RE: [DUG]: Column Names   Nevan,   I wasn't implying that your SQL wasn't normal or correct. I was implying that the resulting renaming of the columns is perfectly normal also and has nothing at all to do with TDataset or Delphi, but the choice of naming on the database server being accessed.   I Agree that it would be nice for the server to more appropriately name columns based on origin, but it is hardly a TDataset function unless they chose to incorporate a SQL processor client side to duplicate the server functionality of interpretation.   regards   Kyley -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Neven MacEwan Sent: Wednesday, 2 October 2002 9:09 a.m. To: Multiple recipients of list delphi Subject: Re: [DUG]: Column Names Kyley   No it isn't   ''Select Store.StoreID, StoreCriteria.StoreID from Store JOIN StoreCriteria on Store.StoreID = StoreCriteria.StoreID"   is perfectly valid SQL, It stems more from the TDataSets non SQL heritage, and yes I could alias the column names but that isn't the point since the column names are unique anyway   Neven ----- Original Message ----- From: Kyley Harris To: Multiple recipients of list delphi Sent: Tuesday, October 01, 2002 5:44 PM Subject: RE: [DUG]: Column Names   it is a standard SQL thing. "select Store.StoreID Store_StoreID, StoreCriteria.StoreID Crit_StoreID" naming them yourself might be more useful to you -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Neven MacEwan Sent: Tuesday, 1 October 2002 1:42 p.m. To: Multiple recipients of list delphi Subject: [DUG]: Column Names Hi all   TADOQuery given a query of "select Store.StoreID, StoreCriteria.StoreID from Store JOIN StoreCriteria on Store.StoreID = StoreCriteria.StoreID" ie 2 columns with the same 'name' but not will cause a fieldname of 'StoreID' and StoreID_1 is there any way to determine which is which ie is 'StoreID' Store.StoreID or  StoreCriteria.StoreID (without parsing the SQL) or is this an problem with TDataSet    Regards Neven  N.K. MacEwan B.E. E&E  Ph 649 621 0001  Fax 649 621 0400  [EMAIL PROTECTED]