From: "Anthony Damico" <ajdamico@gmail.com>
To: "users-list" <users-list@monetdb.org>
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