R sqllite referencing columns with :1 heading
1
0
Entering edit mode
Guest User ★ 13k
@guest-user-4897
Last seen 9.6 years ago
Hi, THis is more an sqllite question, however someone may know the answer here. I am using sqllite to look at cummerbund tables. When I do a self join the duplicate columns have a :1 suffix. I can't seem to reference these columns in further sqllite command using the :1 notation. I've tried to quote the column names, but I aways get a error. Does anyone know how to reference these columns? This code fails(even without quoting A:p_value:1. -- output of sessionInfo(): m1<-"CREATE TABLE r1 as SELECT * INTO r1 FROM geneExpDiffData A INNER JOIN geneExpDiffData B ON A.sample_2 = B.sample_2 WHERE (A.gene_id = B.gene_id) and (A.sample_1 = 'q1') and (B.sample_1 = 'q2') and (A.sample_2 = 'q3') and (A.p_value < 0.1) and (B.p_value < 0.1))" res<-dbGetQuery(cummeRbund:::DB(cuff),m1) #don't know how to specify A.p_value:1 maybe m2<-'SELECT A.gene_id, A.p_value, "A.p_value:1", B.p_value FROM r1 A INNER JOIN geneExpDiffData B ON A.gene_id = B.gene_id WHERE (B.sample_1 = "q1") and (B.sample_2 = "q2") and (B.p_value > 0.1)' -- Sent via the guest posting facility at bioconductor.org.
cummeRbund cummeRbund • 837 views
ADD COMMENT
0
Entering edit mode
@herve-pages-1542
Last seen 12 hours ago
Seattle, WA, United States
Hi Barry, On 06/21/2013 12:52 PM, barry [guest] wrote: > > Hi, > THis is more an sqllite question, however someone may know the answer here. > I am using sqllite to look at cummerbund tables. When I do a self join the duplicate columns have a :1 suffix. I can't seem to reference these columns in further sqllite command using the :1 notation. I've tried to quote the column names, but I aways get a error. Does anyone know how to reference these columns? > > This code fails(even without quoting A:p_value:1. > > -- output of sessionInfo(): > > m1<-"CREATE TABLE r1 as SELECT * INTO r1 FROM geneExpDiffData A INNER JOIN geneExpDiffData B ON A.sample_2 = B.sample_2 WHERE (A.gene_id = B.gene_id) and (A.sample_1 = 'q1') and (B.sample_1 = 'q2') and (A.sample_2 = 'q3') and (A.p_value < 0.1) and (B.p_value < 0.1))" > res<-dbGetQuery(cummeRbund:::DB(cuff),m1) > #don't know how to specify A.p_value:1 maybe > m2<-'SELECT A.gene_id, A.p_value, "A.p_value:1", B.p_value FROM r1 A INNER JOIN geneExpDiffData B ON A.gene_id = B.gene_id WHERE (B.sample_1 = "q1") and (B.sample_2 = "q2") and (B.p_value > 0.1)' > This is not the output of sessionInfo(). Also the above code is not very helpful because (1) it's not self-contained and (2) you're not showing the error you get. I don't see that SQLite adds a :1 suffix to duplicate columns when doing a self join. Using the sqlite3 command line client: CREATE TABLE toto (ii INTEGER, aa TEXT); INSERT INTO toto VALUES (33, 'hello'); INSERT INTO toto VALUES (-5, 'world'); INSERT INTO toto VALUES (33, 'good bye'); Then: sqlite> .header on sqlite> SELECT * FROM toto; ii|aa 33|hello -5|world 33|good bye sqlite> SELECT * FROM toto AS t1 INNER JOIN toto AS t2 ON t1.ii = t2.ii; ii|aa|ii|aa 33|hello|33|good bye 33|hello|33|hello -5|world|-5|world 33|good bye|33|good bye 33|good bye|33|hello Anyway, when you use double quotes to delimit identifiers you need to do this for the individual parts of the fully qualified identifier i.e. A."p_value:1" or "A"."p_value:1" if p_value:1 is a valid column name for table A. HTH, H. > > -- > Sent via the guest posting facility at bioconductor.org. > > _______________________________________________ > Bioconductor mailing list > Bioconductor at r-project.org > https://stat.ethz.ch/mailman/listinfo/bioconductor > Search the archives: http://news.gmane.org/gmane.science.biology.informatics.conductor > -- Hervé Pagès Program in Computational Biology Division of Public Health Sciences Fred Hutchinson Cancer Research Center 1100 Fairview Ave. N, M1-B514 P.O. Box 19024 Seattle, WA 98109-1024 E-mail: hpages at fhcrc.org Phone: (206) 667-5791 Fax: (206) 667-1319
ADD COMMENT

Login before adding your answer.

Traffic: 725 users visited in the last hour
Help About
FAQ
Access RSS
API
Stats

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.

Powered by the version 2.3.6