sqlQuery: impossible to get the ID number as Character into R?

Question

I'm using the RODBC package inside an ORACLE DATA BASE (DB). Everything is doing very well, but I need to obtain a table from this DB and some variables as character type, not numbers.

So, I made my query like this:

e    ManzResul_VIII<-sqlQuery(con,"select distinct t.fono_id_dis,
            t.id_predio,
            t.co_calle,
            t.nu_casa,
            t.x,
            t.y,
            t.plancheta  from c_araya.proy_dist08_todo t  where nvl(t.fono_tipo_dis, '-') not in ('CLIENTE', 'POTENCIAL', 'CARTERA')  and nvl(t.x, 0) <> 0 ")

Is impossible to get the ID number as Character, this query change the type of my iDs variables from Character to Numeric type (The ID has a zero at the beginning, but has been changed it into a number). I have read the function description, but I can see how to manage it.

Any idea would be really appreciated, thanks in advance!

Piety

Does the select statement work as expected outside of R? If it does not then mplourde's answer should work. If it does then try sqlQuery(con,"select ...",as.is=T) – Germanous

Answer 1

Change the query to cast the id to the data type you want:

select distinct cast(t.fono_id_dis as varchar(255)) as id
. . . 

This should work with most databases.

Pulling

Answer 2

This works for me:

library(RODBC)
con <- odbcDriverConnect('driver={SQL Server};server=[your server name];database=[your database name];trusted_connection=true')
ManzResul_VIII<-sqlQuery(con,"select distinct ('m' + id) as id from [your table]")

Zinciferous