[ensembl-dev] SQL sentence gets variation inconsistent with API result

[Laurent Gil]

Hi Gang,

We found the same number of variations using the SQL query, API calls 
(and also the Ensembl Transcript web pages, which uses the API).
Which methods did you use for your perl script ?
To test the data, I used the script below:

use strict;
use warnings;
use Bio::EnsEMBL::Registry;

my $registry = 'Bio::EnsEMBL::Registry';
$registry->load_registry_from_db(
     -host => 'ensembldb.ensembl.org',
     -user => 'anonymous'
);

my $so_term = 'insertion';
my $t_adaptor  = $registry->get_adaptor('human', 'core', 'transcript');
my $tv_adaptor = $registry->get_adaptor('human', 'variation', 
'transcriptvariation');

my @transcripts;
foreach my $t 
('ENST00000419234','ENST00000327551','ENST00000547043','ENST00000539060') {
   push @transcripts, $t_adaptor->fetch_by_stable_id($t);
}

my %variations;
foreach my $tv (@{$tv_adaptor->fetch_all_by_Transcripts(\@transcripts)}) {
   if ($tv->variation_feature->class_SO_term eq $so_term) {
       $variations{$tv->variation_feature->variation_name} = 1;
     }
}
print "Count: ". scalar (keys %variations)."\n";

Cheers,

Laurent
Ensembl Variation

On 14/08/2012 10:17, 陈岗 wrote:
> Hi All
>
> I got variation IDs with SQL sentences, but their numbers are 
> inconsistent with the result from API.
>
> For example, my SQL counts gene ENSG00000089234 has 38 insertions, 45 
> deletions, 5 indels, 0 substitution. However, Ensembl API read out 30 
> insertions, 40 deletions 1 substitutions.
>
> If anyone know the reason that causes the difference, please tell me. 
>  Thanks
>
> My SQL  for insertion:
>
> ( SELECT att.value,vf.variation_name
> FROM transcript_variation tv
> LEFT JOIN variation_feature vf ON ( vf.variation_feature_id = 
> tv.variation_feature_id )
> LEFT JOIN attrib att ON ( att.attrib_id = vf.class_attrib_id )
> WHERE tv.feature_stable_id = 'ENST00000419234'
>      AND att.value = 'insertion' )
>
> union
>
> ( SELECT att.value,vf.variation_name
> FROM transcript_variation tv
> LEFT JOIN variation_feature vf ON ( vf.variation_feature_id = 
> tv.variation_feature_id )
> LEFT JOIN attrib att ON ( att.attrib_id = vf.class_attrib_id )
> WHERE tv.feature_stable_id = 'ENST00000327551'
>      AND att.value = 'insertion' )
>
> union
>
> ( SELECT att.value,vf.variation_name
> FROM transcript_variation tv
> LEFT JOIN variation_feature vf ON ( vf.variation_feature_id = 
> tv.variation_feature_id )
> LEFT JOIN attrib att ON ( att.attrib_id = vf.class_attrib_id )
> WHERE tv.feature_stable_id = 'ENST00000547043'
>      AND att.value = 'insertion' )
>
> union
>
> ( SELECT att.value,vf.variation_name
> FROM transcript_variation tv
> LEFT JOIN variation_feature vf ON ( vf.variation_feature_id = 
> tv.variation_feature_id )
> LEFT JOIN attrib att ON ( att.attrib_id = vf.class_attrib_id )
> WHERE tv.feature_stable_id = 'ENST00000539060'
>      AND att.value = 'insertion' )
>
>
> -- 
> Gang Chen
> TILSI
> Taicang Institute For Life Science Information
> Address: A2/162, Renmin South Road, Taicang, 215400, Jiangsu Province, 
> P.R.China
> Phone: （+86）512-82782588
>
>
>
> _______________________________________________
> Dev mailing list    Dev at ensembl.org
> List admin (including subscribe/unsubscribe): http://lists.ensembl.org/mailman/listinfo/dev
> Ensembl Blog: http://www.ensembl.info/

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.ensembl.org/pipermail/dev_ensembl.org/attachments/20120814/fe15f416/attachment.html>