[BioC] R sqllite referencing columns with :1 heading

[Hervé Pagès]

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