what happens if you use
SELECT gt.chr instead of SELECT fg.chr ?
what happens if you add more parentheses all over the place? for example
(( fg.chr = gt.chr ) AND ( fg.pos >= gt.start_pos ) AND ( fg.pos <= gt.end_pos) )
instead of
(fg.chr = gt.chr AND fg.pos >= gt.start_pos AND fg.pos <= gt.end_pos)
On Mon, Feb 8, 2016 at 3:36 PM, Lynn Carol Johnson lcj34@cornell.edu wrote:
Hi all -
I’m going nuts trying to determine why a monetdb SQL query does not work. I am trying to pull data from 2 tables. These tables are in the DB as shown here:
sql>\d
TABLE maizefullgenome.fullgenomegff
TABLE maizefullgenome.fullgenomestable
TABLE maizefullgenome.gene_from_gff
TABLE maizefullgenome.ranges_from_gff
sql>
If I join only on the “pos” field, the query executes. Here is that query:
delChrPosGene_fullGenome <-dbGetQuery(conn,
"SELECT fg.chr,fg.pos,gt.gene_name FROM maizeFullGenome.fullgenomestable fg
join maizeFullGenome.gene_from_gff gt on (fg.pos >= gt.start_pos AND fg.pos <= gt.end_pos)
where gerp_score> 2 AND gerp_neutral_tree_length > 2
AND ((sift_score_a < 0.05 AND sift_score_a >= -1)
OR (sift_score_c < 0.05 AND sift_score_c >=-1)
OR (sift_score_g < 0.05 AND sift_score_g >=-1)
OR (sift_score_t < 0.05 AND sift_score_t >=-1))")
If I add the “chr” field, it fails with the message below. Can anyone tell me what is wrong with this second query?
delChrPosGene_fullGenome <-dbGetQuery(conn,
"SELECT fg.chr,fg.pos,gt.gene_name FROM maizeFullGenome.fullgenomestable fg
join maizeFullGenome.gene_from_gff gt on (fg.chr = gt.chr AND fg.pos >= gt.start_pos AND fg.pos <= gt.end_pos)
where gerp_score> 2 AND gerp_neutral_tree_length > 2
AND ((sift_score_a < 0.05 AND sift_score_a >= -1)
OR (sift_score_c < 0.05 AND sift_score_c >=-1)
OR (sift_score_g < 0.05 AND sift_score_g >=-1)
OR (sift_score_t < 0.05 AND sift_score_t >=-1))")
Error in .local(conn, statement, ...) :
Unable to execute statement 'SELECT fg.chr,fg.pos,gt.gene_name FROM maizeFullGenome.fullgenomestable fg join maizeFullGenome.gene...'.
Server says '!Object not found'.
Here are the fields in the gene_from_gff table:
sql>\d maizefullgenome.gene_from_gff
CREATE TABLE "maizefullgenome"."gene_from_gff" (
"chr" INTEGER,
"start_pos" INTEGER,
"end_pos" INTEGER,
"gene_name" CHARACTER LARGE OBJECT
);
sql> Here are the fields in the fullgenomestable table:
sql>\d maizefullgenome.fullgenomestable
CREATE TABLE "maizefullgenome"."fullgenomestable" (
"chr" INTEGER,
"pos" INTEGER,
"ref_allele" TINYINT,
"two_bp_upstream" INTEGER,
"two_bp_downstream" INTEGER,
"three_bp_upstream" INTEGER,
"three_bp_center" INTEGER,
"three_bp_downstream" INTEGER,
"four_bp_upstream" INTEGER,
"four_bp_downstream" INTEGER,
"five_bp_upstream" INTEGER,
"five_bp_center" INTEGER,
"five_bp_downstream" INTEGER,
"gc_content" TINYINT,
"gerp_neutral_tree_length" REAL,
"gerp_score" REAL,
"gerp_conserved" BOOLEAN,
"cmethylation" REAL,
"cpg_methylation_1bk_moving_average" REAL,
"chg_methylation_1bk_moving_average" REAL,
"chh_methylation_1bk_moving_average" REAL,
"mnase_low_minus_high_rpm_shoots" REAL,
"mnase_bayes_factor_shoots" REAL,
"mnase_hotspot_shoots" BOOLEAN,
"mnase_low_minus_high_rpm_roots" REAL,
"mnase_bayes_factor_roots" REAL,
"mnase_hotspot_roots" BOOLEAN,
"allele_total_depth" INTEGER,
"site_presence" INTEGER,
"conservedgene_presence" BOOLEAN,
"minor_allele" INTEGER,
"maf" REAL,
"combined_site_uniqueness_score" REAL,
"site_uniqueness_score_sd" REAL,
"expected_invariant_site" BOOLEAN,
"codon_position" TINYINT,
"within_cds" BOOLEAN,
"within_gene" BOOLEAN,
"within_transcript" BOOLEAN,
"within_exon" BOOLEAN,
"within_five_prime_utr" BOOLEAN,
"within_three_prime_utr" BOOLEAN,
"within_mirna" BOOLEAN,
"line_family" INTEGER,
"sine_family" INTEGER,
"ltr_family" INTEGER,
"helitron" BOOLEAN,
"carot_common_support_interval" TINYINT,
"toco_common_support_interval" TINYINT,
"orig_amino_acid_a" TINYINT,
"new_amino_acid_a" TINYINT,
"amino_acid_substitute_pos_a" INTEGER,
"sift_score_a" REAL,
"sift_median_seq_info_a" REAL,
"number_seqs_at_pos_a" INTEGER,
"orig_amino_acid_c" TINYINT,
"new_amino_acid_c" TINYINT,
"amino_acid_substitute_pos_c" INTEGER,
"sift_score_c" REAL,
"sift_median_seq_info_c" REAL,
"number_seqs_at_pos_c" INTEGER,
"orig_amino_acid_g" TINYINT,
"new_amino_acid_g" TINYINT,
"amino_acid_substitute_pos_g" INTEGER,
"sift_score_g" REAL,
"sift_median_seq_info_g" REAL,
"number_seqs_at_pos_g" INTEGER,
"orig_amino_acid_t" TINYINT,
"new_amino_acid_t" TINYINT,
"amino_acid_substitute_pos_t" INTEGER,
"sift_score_t" REAL,
"sift_median_seq_info_t" REAL,
"number_seqs_at_pos_t" INTEGER,
"b73_uniqueness_score" REAL,
"cml247_uniqueness_score" REAL,
"ph207_uniqueness_score" REAL,
"w22_uniqueness_score" REAL,
"coelorachis_anc" TINYINT,
"vossia_anc" TINYINT,
"sorghum_anc" TINYINT,
"oryza_anc" TINYINT,
"setaria_anc" TINYINT,
"brachypodium_anc" TINYINT,
"hordeum_anc" TINYINT,
"musa_anc" TINYINT,
"populus_anc" TINYINT,
"vitis_anc" TINYINT,
"arabidopsis_anc" TINYINT,
"panicum_anc" TINYINT
);
sql>
Any hints are greatly appreciated!
Thanks - Lynn
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list