CODEWITHSUNDEEP
sqlsql-serverdelphiado
Troz
Troz

Reputation: 97

Ambiguous Column name in Sql expression

I have two tables; they are joined by relationship detail on a column name via Delphi ado link

1st table has bunch of data and a fileref as key 1, 2nd table has rows of data and a fileref as key 2

1st table has other information but one fileref value, 2nd table hold many fileref values but different accounts

Table 1: id, fileref, 1, 2, 3, 4, 5, accno, 7, 8, 9, etc, etc...

table 2: id, fileref, accno

  SELECT * FROM vtindex a
  JOIN vi_accno b
  ON b.fileref = a.FileRef
  WHERE (a.AccNo like '%123456789%') or (b.accno like '%123456789%')

Above is the query where i get the ambiguous error

the idea is that if I dont find the accno is table 1 it must try and find it in table 2

hope this makes sense and whats wierd is that if i run the query inside MSSMS the query returns results without errors

Upvotes: 3

Views: 4827

Answers (5)

MartynA
MartynA

Reputation: 30715

I have a hunch that your problem is arising not so much from the Sql you're attempting to use, but the way you are attempting to construct it, using the completely unnecessary and error-prone SQL.Add().

The code below executes correctly and without complaint or error of any sort in D7 against 2 tables in a Sql Server 2014 database.

procedure TForm1.FormCreate(Sender: TObject);
var
  S : String;
begin
  // WARNING: Do not use this Sql in a live application
  // There is a risk of Sql-Injection because  the Sql includes the
  // contents of Edit1.Text.  Use a parameterised query instead!
  S := 'select a.*, b.*'#13#10;
  S := S + 'from TableA a join TableB b'#13#10;  // the #13#10 can be replaced by a single space, 
  //  if you prefer
  S := S + 'on a.fileref = b.fileref'#13#10;
  S := S + 'where (a.accno like ''%' + Edit1.Text + '%'')'#13#10;
  S := S + 'or (b.accno like ''%' + Edit1.Text + '%'')'#13#10;

  AdoQuery1.SQL.Text := S;
  AdoQuery1.Open;
end;

Note the use of single-quotes throughout, no double-quotes.

IMPORTANT Constructing Sql directly from the contents of TEdit controls renders your app liable to Sql Injection (https://en.wikipedia.org/wiki/SQL_injection). You should use parameterised Sql instead. However, having said that, the routine in AdoDB.Pas which parses the Sql to create the parameters, TAdoCommand.ParseSql seems to be broken in D7-Delphi Seattle because it seems incapable of recognising a parameter embedded in a `LIKE' construct which involves a string expression. A way around that might be to define a Stored Proc on the server which executes the SQL with parameters supplied at run-time from the application.

So, my guess is that because you are using SQL.Add(), in fact you're not constructing the Sql you think you are. I suspect that what the error you are getting is actually trying to tell you is that Edit1.Text is ambiguous - depending on your exact actuall Sql, possibly the Sql parser thinks Edit1.Text is the name of a column.

The Sql DDL for TableA and TableB:

CREATE TABLE [dbo].[TableA](
    [ID] [int] NOT NULL,
    [FileRef] [int] NULL,
    [AccNo] [varchar](32) NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[TableB](
    [ID] [int] NOT NULL,
    [FileRef] [int] NULL,
    [AccNo] [varchar](32) NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Upvotes: 1

Vahid Farahmandian
Vahid Farahmandian

Reputation: 6568

Just try replacing * with explicit column names and define unique aliases for each of the columns. This will solve the ambiguity error. Following is an example:

Updated:

SELECT a.*,b.Id as b_Id, b.Fileref as b_Fileref, b.accno as b_accno FROM vtindex a
JOIN vi_accno b
ON b.fileref = a.FileRef
WHERE (a.AccNo like '%123456789%') or (b.accno like '%123456789%')

As @Joe C commented too, we think you do not need to send FileRef and b.Id columns inside vi_accno to the output, if our assumption is right, then you can remove them from your Select and simplify it as follow:

SELECT a.*, b.accno as b_accno FROM vtindex a
JOIN vi_accno b
ON b.fileref = a.FileRef
WHERE (a.AccNo like '%123456789%') or (b.accno like '%123456789%')

Upvotes: 2

Joe C
Joe C

Reputation: 3993

You have the same column name in both tables, so using * pulls both and the names conflict. In SSMS you are allowed to do that as you are only viewing the results on screen but as soon as you send the data to a destination that expects column names to be unique you will get an error. You will have to explicitly name the columns you want in your select list.

Also, based on your question I believe you will want to use coalesce(b.accno, a.accno). This will use the accno from A only if accno is null in B.

EDIT: Based on comments I believe here is the specific syntax I was referring to. Note how coalesce solve the problem of column aliases by having only one field with the name you want.

SELECT ID, FileRef, cnum, Month, Type, Typei, 
       PropDesc, Coalesce(a.accno, b.accno) AccNo, person, client, idno,        
       Consultant, Memo, qclose, vtdate 
    FROM vtindex a
    Join vi_accno b ON b.fileref = a.FileRef
    WHERE (a.AccNo like '%123456789%') or (b.accno like '%123456789%')

Upvotes: 2

Rich Benner
Rich Benner

Reputation: 8113

You're going to have to declare your columns, something like this;

SELECT
     a.ID A_ID
    ,a.fileref A_fileref
    ,a.Field1 A_Field1
    ,a.Field2 A_Field2
    ,a.accno A_Accno
    ,b.id B_ID
    ,b.fileref B_fileref
    ,b.accno B_accno
FROM vtindex a
JOIN vi_accno b
    ON a.fileref = b.fileref
WHERE a.AccNo like '%123456789%'
    OR b.accno like '%123456789%'

If you only want to declare some then do this;

SELECT
     a.*
    ,b.id B_ID
    ,b.fileref B_fileref
    ,b.accno B_accno
FROM vtindex a
JOIN vi_accno b
    ON a.fileref = b.fileref
WHERE a.AccNo like '%123456789%'
    OR b.accno like '%123456789%'

Your issue is that you're using fields like id, fileref and accno more than once (from each table) and the names are clashing. If you change the names on your table that only has 3 records then you can get away with leaving table a as-is.

Upvotes: 5

DineshDB
DineshDB

Reputation: 6193

Try this,

SELECT a.*,b.* FROM vtindex a
    JOIN vi_accno b
    ON b.fileref = a.FileRef
    WHERE (a.AccNo like '%123456789%') or (b.accno like '%123456789%')

Upvotes: 1

Related Questions