Hi Syed, Here is the XML equivalent of the query that works: http://pastebin.com/gcNrKnM8
Thanks, Nelson > Sorry Nelson, i might have missed few details, but could you send me the > XML equivalent of the query ? > > > On 27/09/2011 15:20, [email protected] wrote: >> Hi Junjun& Syed, >> I still have problems with this earlier reported query. So I have tried >> to >> tweak a few parameters (listed below) as I generated error logs, >> hopefully >> these will be more useful to diagnosing the problem. >> >> You correctly pointed out the query involves a join between two datasets >> linked by exportables and importables each of which has 2 >> attributes/filters (based on ensembl gene and uniprot id attributes). >> >> This query works with an old version of our database but it throws the >> SQL >> syntax error with the new version of the database. >> I have tried to perform a few tests and saved the error_log files. I >> hope >> these will shed more light. >> >> You had mentioned that something might be missing before the 'AND' and >> this reminded me that there possibly might be a NULL value on one of the >> tables that could trigger an ERROR (I have encountered this before, and >> I >> got a patch from Damian on the FilterList.pm module to fix such a bug, >> he >> later committed this to the BioMart code). Since this mart is on a >> different server from the one that had the patch, I thought I would try >> testing with the patch as well. >> >> Like Syed had asked, the query in question is sent to the server via >> webservice. >> >> >> Some information about the error_logs from each test: >> >> ================================================= >> These are logs for the query when its not working >> ================================================= >> >> About error_log1: http://pastebin.com/3CKRzTvc >> 1. Deleted the two_variable_id exportable/importables both on EWAS and >> Pathway datasets. >> 2. Used new version db (v38_mart). >> 3. Query of interest not working. >> 4. Used FilterList.pm module that was altered by Damian. >> >> >> About error_log2: http://pastebin.com/ekVihAn9 >> 1. Deleted the two_variable_id exportable/importables both on EWAS and >> Pathway datasets. >> 2. Used new version db (v38_mart). >> 3. Query of interest not working. >> 4. Used original FilterList.pm module. >> >> >> About error_log3: http://pastebin.com/ZLagmDQh >> 1. Has two_variable_id exportable/importables both on EWAS and Pathway >> datasets. >> 2. Used new version db (v38_mart). >> 3. Query of interest not working. >> 4. Used FilterList.pm module that was altered by Damian. >> >> ========================================== >> These are logs for the query when it works >> ========================================== >> >> About error_log4: http://pastebin.com/prAgP3vS >> 1. Has two_variable_id exportable/importables both on EWAS and Pathway >> datasets. >> 2. Used old version db (v35_mart). >> 3. Query of interest Works. >> 4. Used original FilterList.pm module. >> >> >> About error_log5: http://pastebin.com/gS8HghkT >> 1. Has two_variable_id exportable/importables both on EWAS and Pathway >> datasets. >> 2. Used old version db (v35_mart). >> 3. Query of interest Works. >> 4. Used FilterList.pm module that was altered by Damian. >> >> >> >> Thanks, >> Nelson >> >> >> >> >> >>> This SQL doesn't look correct, something is missing right before the >>> 'AND' >>> for the part that is joined with 'OR', with the exception of the last >>> item >>> as below: >>> >>> ((pathway__referencepeptidesequence__dm.referencedatabase_uniprot = >>> 'P49327') AND >>> (pathway__referencednasequence__dm.referencedatabase_ensembl >>> = 'ENSG00000169710')) >>> >>> >>> It looks like the original query involves join between two datasets. >>> Usually that is not a problem when the link defined as an >>> exportable/importable pair, in which exportable contains one attribute >>> (eg, ensembl_gene) and importable contains one filter (eg, >>> ensembl_gene). >>> The piece of SQL statement shown above makes me thinking that the link >>> you >>> defined seemed to have two attributes and two filters (ensembl gene and >>> uniprot id) for its exportable and importable. >>> >>> Although it is legitimate to create such a link, but I am afraid it is >>> not >>> going to work in most of the cases. It works only when *one* pair of >>> the >>> values is exported from one dataset and imported to the other dataset. >>> The >>> 'band' filter works exactly like this in Ensembl gene mart, user >>> specifies >>> *one* band to a hidden dataset which exports *one* pair (ie, one row) >>> of >>> 'chromosome' and 'coordinate' value (eg, 1, 10000303), and ensembl_gene >>> dataset imports this pair and stick it in to SQL that queries against >>> it's >>> own db. >>> >>> The confirm this, you can double check the exportable/importable >>> settings >>> in your configuration. >>> >>> Hope this helps. Let us know if you have any further questions. >>> >>> Best regards, >>> >>> Junjun >>> >>> >>> >>> >>> On 11-09-26 9:47 AM, "[email protected]"<[email protected]> wrote: >>> >>>> Hi, >>>> I am getting an error from one of my queries along the lines of SQL >>>> syntax >>>> being the cause, am not sure what the real problem is though. I set >>>> the >>>> log4perl.conf to debug mode and I got back a more elaborate error >>>> report >>>> http://pastebin.com/9rqKBfWR >>>> Anyone know what the problem is? >>>> Thanks, >>>> Nelson >>>> >>>> Serious Error: Error during query execution: You have an error in your >>>> SQL >>>> syntax; check the manual that corresponds to your MySQL server version >>>> for >>>> the right syntax to use near 'AND >>>> (pathway__referencednasequence__dm.referencedatabase_ensembl = >>>> 'ENSG00000166' at line 1 >>>> >>>> ERROR: caught BioMart::Exception::Database: Error during query >>>> execution: >>>> You have an error in your SQL syntax; check the manual that >>>> corresponds >>>> to >>>> your MySQL server version for the right syntax to use near 'AND >>>> (pathway__referencednasequence__dm.referencedatabase_ensembl = >>>> 'ENSG00000166' at line 1 >>>> >>>> _______________________________________________ >>>> Users mailing list >>>> [email protected] >>>> https://lists.biomart.org/mailman/listinfo/users >>> >>> >> >> >> >> >> _______________________________________________ >> Users mailing list >> [email protected] >> https://lists.biomart.org/mailman/listinfo/users > _______________________________________________ Users mailing list [email protected] https://lists.biomart.org/mailman/listinfo/users
