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

[Luis Rideau Cruz]

(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 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