function case in sqldf (datas from oracle) with a null value
On Mon, Aug 20, 2012 at 7:27 AM, cindy.dol <cindy.dolomieu at insa-lyon.fr> wrote:
I use sqldf to join 2 dataframes from 2 distinct databases : a and b come
from old sqldf's.
sqldf("select a.*, b.*, case a.QTY when null then b.QTY else a.QTY end as
NEW_QTY"
from a inner join b on a.OBJECT=b.OBJECT")
R doesn't understand "when null". I tried with "when NA", "when '' ", "when
' ' " but it doesn't work.
What does Oracle have to do with all this? Since its not clear what your set up is here is a reproducible example along the lines of your sql statement. BOD is a six row, two column data frame that comes with R. We insert some NAs into two replicas of it:
library(sqldf)
# test data
BODa <- BODb <- BOD
BODa[1, 2] <- BODb[2, 2] <- NA
sqldf("select Time, case when a.demand is null then b.demand else a.demand end as demand from BODa a join BODb b using(Time)")
Time demand 1 1 8.3 2 2 10.3 3 3 19.0 4 4 16.0 5 5 15.6 6 7 19.8
Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com