Page 1 of 1
Ambiguous field name between two tables
Posted: Wed 20 Oct 2010 02:27
by inageib
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
Posted: Wed 20 Oct 2010 02:31
by inageib
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 ?
Posted: Wed 20 Oct 2010 13:43
by AndreyZ
Hello,
I cannot reproduce the problem. Please send your scripts to create the tables to andreyz*devart*com
Posted: Wed 20 Oct 2010 18:23
by inageib
sent, thanks alot
Posted: Wed 20 Oct 2010 20:14
by inageib
BTW I use Firebird 64bit edition (latest stable version)
Posted: Thu 21 Oct 2010 08:52
by AndreyZ
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.
Posted: Fri 22 Oct 2010 04:00
by inageib
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 ; ^
Posted: Fri 22 Oct 2010 08:30
by AndreyZ
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
Posted: Fri 22 Oct 2010 13:44
by inageib
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