[R] function case in sqldf (datas from oracle) with a null value

[Gabor Grothendieck]

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