Hi Richard and Damian,
I get server error (code 500) if I select to filter of any of the fields
of the mid__timepoint__main or the mid__post_infection__dm tables.\
I have selected "Naive" to create a basic configuration which I then
exported, but the error remains.
Below is my table schema for biomart
CREATE TABLE mid__sample__main
(
sample_id_key INTEGER NOT NULL,
study_id VARCHAR(20),
location VARCHAR(20),
participant_id INTEGER,
PRIMARY KEY (sample_id_key)
) TYPE=MyISAM;
CREATE TABLE mid__timepoint__main
(
timepoint_id_key INTEGER NOT NULL,
sample_id_key INTEGER NOT NULL,
study_id VARCHAR(20),
location VARCHAR(20),
participant_id INTEGER,
phase INTEGER,
visit INTEGER,
interim_visit INTEGER,
year INTEGER,
month INTEGER,
day INTEGER,
PRIMARY KEY (timepoint_id_key),
FOREIGN KEY (sample_id_key) REFERENCES mid__sample__main(sample_id_key)
) TYPE=MyISAM;
CREATE TABLE mid__period_post_infection__dm
(
timepoint_id_key INTEGER NOT NULL,
days_post_infection INTEGER,
weeks_post_infection INTEGER,
infection_year INTEGER,
infection_month INTEGER,
infection_day INTEGER,
PRIMARY KEY (timepoint_id_key),
FOREIGN KEY(timepoint_id_key)REFERENCES
mid__timepoint__main(timepoint_id_key)
)TYPE=MyISAM;
On Wed, 28 Jun 2006, Richard Holland wrote:
I might be wrong here (someone please correct me if I am) but you can
only have multiple main tables if there is a 1:M relation between them -
i.e. every B includes all the fields of A plus some extra fields, some
of which make B unique when looked at in combination with the fields
inherited from A. Dimension tables that refer to A must include all the
key columns for A, and those that refer to B must refer to all the key
columns for B (which by definition will include all the key columns that
B inherited from A).
Could you check that this is the case?
what Richard is saying is correct. From the error message it seems your
second main table is prob missing the key from the first main table (ie)
the second main needs all the cols from the first one including the key.
cheers
damian
cheers,
Richard
On Wed, 2006-06-28 at 11:46 +0200, allank wrote:
Hi all,
I am unable to query from a biomart schema having two tables and (at the
moment) one dimension table, I get a " SQL FAILED - LIKELY XML
MISCONFIGURATION ERROR ..." error at the result display stage on the
biomart web application.
I have setup the biomart table structure in accordance with the multiple
main table requirements, in Martj I "validate all" and I get no errors,
when using the web application, I am able to go smoothly through the
filter process and the field-section-for-output stages the pane at the
right indicates I have 32 entries at the output level, when I attempt to
proceed to the output display page, I get the message " SQL FAILED -
LIKELY XML MISCONFIGURATION ERRORUnknown column 'main.timepoint_id_key'
in 'where clause'".
Could the error have been caused from using a key field (in my case
timepoint_id_key) for fields from the dimension table that is not found
in all the main tables. If this is so, how can I manage the situation
where I have a main table (dataset__A__main) having a one to many
relationship with one dimension table (dataset__C__dm) and other main
table (dataset__B__main) which will have one to many relationship with
several dimension tables.
Allan.
--
Richard Holland (BioMart Team)
EMBL-EBI
Wellcome Trust Genome Campus
Hinxton
Cambridge CB10 1SD
UNITED KINGDOM
Tel: +44-(0)1223-494416
