cindy.dol
2012-Aug-20 11:27 UTC
[R] 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.
Gabor Grothendieck
2012-Aug-20 14:36 UTC
[R] 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