2

I typically use functions such as is.na and COALESCE but I cannot find such as functions for R's SQLDF which makes its usage hard with messy data with missing values. Is there some way to force SQLDF to return NA in the case when NAs are occurring in the operations?

How can you handle missing values and NAs with SQLDF?

> sqldf('SELECT NA/13210')

Exception: no such column: NA
Show stack trace
> sqldf('SELECT NA/13210, 223423/NA')

Exception: no such column: NA
Show stack trace
hhh
  • 50,788
  • 62
  • 179
  • 282

1 Answers1

2

The code in the SQL string must be SQL, not R. Use

sqldf('SELECT Null/13210')
##   Null/13210
## 1         NA

Here is noather example

dd <- data.frame(x = NA)
sqldf("select x/13120 from dd")
##   x/13120
## 1      NA
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
  • I am getting `` returned in the table from sqldf, how can I state that do not contain row with any NA with sqldf? I tried `col IS NOT NA`, `col IS NOT Null`. – hhh Feb 22 '20 at 15:41
  • 1
    As the answer says. You must use SQL. You can't use R in the SQL string. NA is R, not SQL. Using the built in `BOD` data frame `sqldf("select * from BOD where Time is not null")` will return all columns since no row has an NA in the Time column. – G. Grothendieck Feb 22 '20 at 15:58