Re: Debugging: !SELECT: no such operator 'normalize2'

15 Mar 2016

      Hi all, 

I have been able to get it to work, but in a stranger way. (and less easy for me to use later on) 
When I use this: 
dbGetQuery(conn, "SELECT * FROM normalize2( (SELECT barcode, welltype_code, cell_gfp2_rfp_pearson_cor_mean_raw_value FROM hcs) );") 

It works! 
The downside of this, is that need to perform this operation on many columns of the table. 
With the normalize function I could do the following: 
stmt <- "SELECT barcode, normalize(welltype_code, data_column1), normalize(welltype_code, data_column2) FROM hcs;" 
dbGetQuery(conn, stmt) 

I am not sure how to accomplish a similar result with the new query structure, apart from joining up the intermediate tables. 
(And since this operation has to be done on thousands of columns, that will probably not be the most efficient strategy) 

Kind regards, 

Willem
...
From: "Willem Ligtenberg" 
To: "users-list" 
Sent: Tuesday, March 15, 2016 1:29:37 PM
Subject: Re: Debugging: !SELECT: no such operator 'normalize2'
...
Hi Anthony,
...
Sure, here is the dput output:
structure(list(barcode = c(110000184638, 110000184638, 110000184638,
110000184638, 110000184638, 110000184638, 110000184638, 110000184638,
110000184638, 110000184638), welltype_code = c("LC", "LC", "LC",
"LC", "LC", "LC", "LC", "LC", "LC", "LC"), data_column1 = c(0.344772189855576,
0.334164410829544, 0.315271258354187, 0.320378184318542, 0.322041183710098,
0.32072114944458, 0.29565417766571, 0.321962893009186, 0.298929244279861,
0.323741465806961)), .Names = c("barcode", "welltype_code", "data_column1"
), row.names = c(NA, 10L), class = "data.frame")
...
Kind regards,
...
Willem
...
...
From: "Anthony Damico" 
To: "users-list" 
Sent: Tuesday, March 15, 2016 11:27:32 AM
Subject: Re: Debugging: !SELECT: no such operator 'normalize2'
...
...
hi, when you send something like this, could you make it a minimal reproducible
example so we can quickly get to the exact point of the problem?
...
...
dput( head( mtcars ) )
...
...
gives you output which then you could use as this text below so we instantly
have data for a call like `dbWriteTable( db , 'yourtable' , x )`
...
...
x <- structure(list(mpg = c(21, 21, 22.8, 21.4, 18.7, 18.1), cyl = c(6,
6, 4, 6, 8, 6), disp = c(160, 160, 108, 258, 360, 225), hp = c(110,
110, 93, 110, 175, 105), drat = c(3.9, 3.9, 3.85, 3.08, 3.15,
2.76), wt = c(2.62, 2.875, 2.32, 3.215, 3.44, 3.46), qsec = c(16.46,
17.02, 18.61, 19.44, 17.02, 20.22), vs = c(0, 0, 1, 1, 0, 1),
am = c(1, 1, 1, 0, 0, 0), gear = c(4, 4, 4, 3, 3, 3), carb = c(4,
4, 1, 1, 2, 1)), .Names = c("mpg", "cyl", "disp", "hp", "drat",
"wt", "qsec", "vs", "am", "gear", "carb"), row.names = c("Mazda RX4",
"Mazda RX4 Wag", "Datsun 710", "Hornet 4 Drive", "Hornet Sportabout",
"Valiant"), class = "data.frame")
...
...
On Tue, Mar 15, 2016 at 6:16 AM, Willem Ligtenberg <
willem.ligtenberg@openanalytics.eu > wrote:
...
...
...
I have created a function in R for MonetDB.
My first function works fine:
...
...
...
dbGetQuery(conn, "DROP FUNCTION normalize;")
functionDef <- paste(
"CREATE FUNCTION normalize(welltype STRING, data_column DOUBLE) RETURNS TABLE (i
DOUBLE) LANGUAGE R {",
"idx <- which(welltype == 'LC')",
"100 * data_column / median(data_column[idx])",
"};", sep = "\n")
dbGetQuery(conn, functionDef)
...
...
...
# example use
stmt <- "SELECT barcode, normalize(welltype_code, data_column1) FROM hcs;"
dbGetQuery(conn, stmt)
# Works! Yay!
...
...
...
So I wanted to create a more advanced version:
dbGetQuery(conn, "DROP FUNCTION normalize2;")
functionDef <- paste(
"CREATE FUNCTION normalize2(barcode DOUBLE, welltype STRING, data_column DOUBLE)
RETURNS TABLE (i DOUBLE) LANGUAGE R {",
"idx <- which(welltype == 'LC')",
"100 * data_column / median(data_column[idx])",
"};", sep = "\n")
dbGetQuery(conn, functionDef)
...
...
...
As you see, the only thing that I have changed so far, is the addition of an
extra argument to the function.
However, this fails:
# example use
stmt <- "SELECT normalize2(barcode, welltype_code, data_column1) FROM hcs;"
dbGetQuery(conn, stmt)
...
...
...
I checked, and the types are all fine:
barcode = DOUBLE
welltype_code = CHARACTER LARGE OBJECT
data_column1 = DOUBLE
...
...
...
So what is going wrong?
...
...
...
Kind regards,
...
...
...
Willem
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
...
...
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
...
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list