Luis Rideau Cruz
2005-Jan-12 11:50 UTC
[R] RODBC package -- sqlQuery(channel,.....,nullstring=0)stillgives NA's
(1) I do read the posting guide (the fact that I missread o missunderstood something does not imply not reading) (2) I could change NAs to 0 (I know) but I have previously (older versions of R and SQL*Plus) used the same select with the "right" output (namely with 0s). (3) AFAIK "strange" is not a negative remark and does not seem to me at the very least but that is always a matter of taste. (4) Thank you for your replies but the door is still open so as to know a solution to the select without coercing NAs to 0s after retrieving the data Best, Luis>>> Prof Brian Ripley <ripley at stats.ox.ac.uk> 12/01/2005 11:21:33 >>>PLEASE do read the help page, which says nullstring: character string to be used when reading 'SQL_NULL_DATA' character items from the database. ^^^^^^^^^^^^^^^ so this does not apply to numeric items. You can of course easily change numeric NAs to 0s, if you want to. On Wed, 12 Jan 2005, Luis Rideau Cruz wrote:> There is something strange in R behaviour (perhaps).Your negative remarks are not appreciated.> I have run the same select in Oracle SQL*Plus (version 10.1.0.2.0)and> the output comes out with NULLs (which is what it ougth to be). > > But in R I still get the same result with NAs (no matter I use > na.strings or nullstring arguments) > An output example follows below: > > Using na.string="0" and nullstring="0" (sorry by the indents): > > Length 2003 2002 2001 2000 1999 1998 1997 1996 1995 > 1 32 NA 1 NA NA NA NA NA 2 NA > 2 34 3 NA NA NA NA NA NA 6 NA > 3 35 NA NA NA NA 2 NA NA NA NA > 4 36 NA 12 NA NA 10 NA NA 1 NA > 5 37 3 3 NA NA 4 NA NA 31 NA > 6 38 2 4 1 1 12 6 NA 11 NA > 7 39 4 13 5 5 34 8 NA 58 13 > > Length 2003 2002 2001 2000 1999 1998 1997 1996 1995 > 32 1 > 2 > 34 3 > 6 > 35 2 > 36 12 10 > 1 > 37 3 3 4 > 31 > 38 2 4 1 1 12 > 6 11 > 39 4 13 5 5 34 > 8 58 13 > > > Best, > Luis > > >>>> Prof Brian Ripley <ripley at stats.ox.ac.uk> 12/01/2005 09:14:22 >>> > On Tue, 11 Jan 2005, Luis Rideau Cruz wrote: > >> R-help, >> >> I'm using the RODBC package to retrieve data froma ODBC database > which >> contain NA's. >> >> By using the argument nullstring = "0" in sqlQuery() I expect to >> coerce them to numeric but still get NA's in my select. > > You need to read the help page (as the posting guide asks): it says > > na.strings: character string(s) to be mapped to 'NA' when reading > character data. > > which is the opposite of what you are saying you want to do. > > An ODBC database cannot contain NA's. It may contain NULLs, and itmay> > contain "NA", so we have no idea what you mean. > > -- > Brian D. Ripley, ripley at stats.ox.ac.uk > Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/> University of Oxford, Tel: +44 1865 272861 (self) > 1 South Parks Road, +44 1865 272866 (PA) > Oxford OX1 3TG, UK Fax: +44 1865 272595 > >-- Brian D. Ripley, ripley at stats.ox.ac.uk Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595
Seemingly Similar Threads
- RODBC package -- sqlQuery(channel,.....,nullstring=0) stillgives NA's
- RODBC package -- sqlQuery(channel,.....,nullstring=0) still gives NA's
- RODBC and NULL values
- RODBC repeated rows problem
- quoting a table name due to a special character in sqlQuery (RODBC)