2

I have a select which joins the 2 tables, so there are columns with similar names from these tables, so now when retrieving the results FireDac adds underscore 1 to the 2nd column name to differentiate between the 2

Select * from Table1 inner join Table2 on 
Table1.id = Table2.id

When getting records from fields with similar names I would have to do this...

Dataset.FieldByName('Name').asInteger
Dataset.FieldByName('Name_1').asInteger

I would like to know if there is a setting to change the _1 to something else, reason I want to change this is that we used to use Interbase which added just a 1.

Alec
  • 569
  • 2
  • 17
  • 27

2 Answers2

1

I'm not aware of such configuration. only option I can think of is not to use SELECT * but select the columns that you actually need with aliases for the same column names e.g. 

SELECT Table1.Name, Table2.Name AS Name1, ... from Table1 inner join Table2 on 
Table1.id = Table2.id

or, if you need all columns from Table1 and you must use SELECT * you can use:

SELECT Table1.*, Table2.Name AS Name1, ...

See also: Why is “Select * from table” considered bad practice

Community
  • 1
  • 1
kobik
  • 21,001
  • 4
  • 61
  • 121
0

Don't use select * from. Name the columns you want to fetch, define aliases for them and use their table names (or their aliases) to create unique names.

select t1.Name as Name1, t2.Name as Name2 from Table1 t1 inner join Table2 t2 on t1.Name = t2.Name

You will now be able to get their values this way

Dataset.FieldByName('Name1').asInteger;
Wosi
  • 41,986
  • 17
  • 75
  • 82
  • 2
    I am just wondering. Is the addressing field like 't1.Name' a feature of FireDac? – Z.B. Nov 01 '16 at 11:05
  • 2
    Are you sure this would work in FireDac? this does not work with ADO and has the same behavior as described by OP. – kobik Nov 01 '16 at 11:06
  • @kobik. Exactly - that's what I first checked :) – Z.B. Nov 01 '16 at 11:07
  • I am actually trying to avoid using aliases – Alec Nov 01 '16 at 11:50
  • @Fero68 Why? Aliases help you solving your problem. There are good reasons to use them. Otherwise you'd need to name the columns differently in your database – Wosi Nov 01 '16 at 15:11