[R] RODBC package -- sqlQuery(channel,.....,nullstring=0) stillgives NA's

[Prof Brian Ripley]

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 it may
>
> 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