7

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!

Al Fahad
  • 2,378
  • 5
  • 28
  • 37
José Bustos
  • 159
  • 1
  • 7
  • 5
    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)` – Seth May 30 '12 at 20:15

2 Answers2

2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

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]")
Cherry Wu
  • 3,844
  • 9
  • 43
  • 63