R help - Aug 2012 - function case in sqldf (datas from oracle) with a null value

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.



--
View this message in context:
http://r.789695.n4.nabble.com/function-case-in-sqldf-datas-from-oracle-with-a-null-value-tp4640772.html
Sent from the R help mailing list archive at Nabble.com.

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