-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 This looks like SQL that has been auto-generated by MartBuilder 0.5. If this is the case, then you must have found the bug I was hoping nobody would find. :)
I don't know what to suggest really as I don't know much about your schema. However, if it is the bug I knew about already, then this has been fixed already in the latest head branch of martj CVS. Having said that, the head branch of martj CVS is rapidly changing and largely untested code and contains some significant changes that do not match up with the 0.5 documentation, so only go there if you're feeling brave! :) In the meantime, all I can suggest is to modify the offending SQL by hand before running it. This should be relatively straightforward as you have already discovered the solution and know how to fix it, and it will always only be the first join statement for each final table that is affected. If you save your SQL per-table you will only need to update the first join statement in each resulting SQL file. I am perfectly aware that this is not the ideal solution, but I cannot see an alternative for now. If it is the bug I knew about, then there in fact is no way to fix it in 0.5 due to the internal design of MartBuilder. The fix for 0.6 involved a complete rewrite of the algorithm which works out the SQL. That rewrite is still being tested. Sorry about that - I promise 0.6 will be better! cheers, Richard Arek Kasprzyk wrote: > > On 14 Feb 2007, at 14:05, trevor paterson ((RI)) wrote: > >> Hi >> >> I am looking for a bit of help trying to start using biomart >> >> the very first dataset i try to create has an sql error in it, in the >> very first table, which to my mind looks like an error in the >> autogeneration - not something I have configured >> >> >> >> create table testbiomart.TEMP__1 as select a.email_address as >> email_address,a.first_name as first_name,a.institution as >> institution,a.password as password,a.surname as surname,a.user_id as >> user_id_key,b.created as created,b.name as name,b.type_name as >> type_name,b.user_id as user_id_core_object from public.users as a left >> join public.core_object as b on a.user_id_key=b.accession; >> >> the last condition refers to a non existant column a.user_id_key, >> which should actually be a.user_id ( because user_id_key is the name >> generated for the new field, not the original datasource name...) >> >> i suspect it is not easy to recongifure things like this - but am i >> missing something?? >> >> I am registering for the dev list - but this seems to be 'non-instant' >> > > Hi Trevor, > hmm ... it should be instant :) > I have subscribed you now > > your table looks perfectly ok, apart from the fact that it does not > comply with biomart naming convention > but this would only cause problems later on during configuration step. > In fact 'create table as select ...' > is is how we generate all our marts > > Could you send us the exact SQL error you seeing? > we'll see if we can help > > (just a guess ... perhaps 'from public.users as a' is not correct, maybe > 'from public.users a' should be ok?) > what rdbms are you working with? > > a. > > > > >> ta >> >> Trevor Paterson >> >> Bioinformatics >> Roslin Institute >> Scotland EH25 9PS >> phone +44 (0) 131 5274477 >> http://www.roslin.ac.uk/ >> http://www.thearkdb.org/ >> http://www.comparagrid.org/ >> >> >> Roslin Institute is a company limited by guarantee, registered in >> Scotland (registered number SC157100) and a Scottish Charity >> (registered number SC023592). Our registered office is at Roslin, >> Midlothian, EH25 9PS. VAT registration number 847380013. >> >> The information contained in this e-mail (including any attachments) >> is confidential and is intended for the use of the addressee only. >> The opinions expressed within this e-mail (including any attachments) >> are the opinions of the sender and do not necessarily constitute those >> of Roslin Institute (Edinburgh) ("the Institute") unless specifically >> stated by a sender who is duly authorised to do so on behalf of the >> Institute >> >> > > > ------------------------------------------------------------------------------- > > Arek Kasprzyk > EMBL-European Bioinformatics Institute. > Wellcome Trust Genome Campus, Hinxton, > Cambridge CB10 1SD, UK. > Tel: +44-(0)1223-494606 > Fax: +44-(0)1223-494468 > ------------------------------------------------------------------------------- > > > > -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2.2 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFF00LL4C5LeMEKA/QRAnhQAJ9m8BSbum1SYo9Z3pewyL530nRxIACggREm XiBpBcjPe1xaG1aVzyRfDFo= =tYVj -----END PGP SIGNATURE-----
