Ambiguous field name between two tables

inageib · Post by **inageib** » Wed 20 Oct 2010 02:27

hi
I have three tables: series, models and series_model, the series model take values from both series and model tables
and I defined a join table query like this :

select
series_model.sm_id,
series_model.series_id,
series_model.model_id,
series.series_name,
series.series_description,
models.model_name,
models.model_description
from models
right outer join series_model on (models.model_id = series_model.model_id)
left outer join series on (series_model.series_id = series.series_id)
where series_id = :series_id

when I try to open the series_model table (tibcquery) I get this error:

Dynamic SQL Error
SQL error code = -204
Ambiguous field name between table SERIES_MODEL and table SERIES
SERIES_ID.

please advise

thanks

inageib · Post by **inageib** » Wed 20 Oct 2010 02:31

I found the solution , in the where clause I should write like this:

series_model.series_id

by the way doing a master detail using the inspector properties also display the error and no solution other than doing this in SQL like I did !!, why ?

AndreyZ · Post by **AndreyZ** » Wed 20 Oct 2010 13:43

Hello,

I cannot reproduce the problem. Please send your scripts to create the tables to andreyz*devart*com

inageib · Post by **inageib** » Wed 20 Oct 2010 18:23

sent, thanks alot

inageib · Post by **inageib** » Wed 20 Oct 2010 20:14

BTW I use Firebird 64bit edition (latest stable version)

AndreyZ · Post by **AndreyZ** » Thu 21 Oct 2010 08:52

I still cannot reproduce the problem. Please send me a complete small sample to andreyz*devart*com to demonstrate the problem, including a full script (you didn't send me the script for MODELS table creation) to create the tables.
Also supply me the following information:
- the exact version of IBDAC. You can see it in the About sheet of TIBConnection Editor;
- the exact version of your IDE;
- the exact version of Firebird.

inageib · Post by **inageib** » Fri 22 Oct 2010 04:00

FB: 2.1.3.18185 (64bit edition)
IBDAC: 3.10.0.16 (I know it is not the last version but I dont have time to setup last version !!! is it a bug in older version ?)

steps to reproduce: make a join sql in the first post for detail table >> use master /detail properties in the inspector >> try to open the detail table >> error message popup

Model table:

Code: Select all

CREATE GENERATOR GEN_MODELS_ID;

CREATE TABLE MODELS (
    MODEL_ID                SMALLINT NOT NULL,
    MODEL_NAME              VARCHAR(8) CHARACTER SET NONE NOT NULL,
    MODEL_DESCRIPTION       VARCHAR(45) CHARACTER SET NONE NOT NULL,
    NUM_VALUE               FLOAT DEFAULT 1.0,
    OPERATOR                VARCHAR(20) DEFAULT USER,
    DATE_SAVED              TIMESTAMP DEFAULT 'NOW',
    OPERATOR_LAST_MODIFIED  VARCHAR(20),
    DATE_LAST_MODIFIED      TIMESTAMP
);




/******************************************************************************/
/***                              Primary Keys                              ***/
/******************************************************************************/

ALTER TABLE MODELS ADD CONSTRAINT PK_MODELS PRIMARY KEY (MODEL_ID);


/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/


SET TERM ^ ;



/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: MODELS_BI */
CREATE OR ALTER TRIGGER MODELS_BI FOR MODELS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.MODEL_ID IS NULL) THEN
    NEW.MODEL_ID = GEN_ID(GEN_MODELS_ID,1);
END
^


SET TERM ; ^

AndreyZ · Post by **AndreyZ** » Fri 22 Oct 2010 08:30

When you establish master/detail relationship one parameter for masterfield is created automatically. And one more parameter is created for parameter in the SQL code. Therefore, to solve the problem you have to set the following properties of the TIBCQuery ( your detail table ) component:

Code: Select all

SQL=
select 
series_model.sm_id, 
series_model.series_id, 
series_model.model_id, 
series.series_name, 
series.series_description, 
models.model_name, 
models.model_description 
from models 
right outer join series_model on (models.model_id = series_model.model_id) 
left outer join series on (series_model.series_id = series.series_id)

MasterSource=Your_MasterSource
MasterFields=SERIES_ID
DetailFields=SERIES_MODEL.SERIES_ID

inageib · Post by **inageib** » Fri 22 Oct 2010 13:44

ok and I hope you do a fix in the future to avoid this error, since using the master and detail properties increase performance.

thanks