Home > Blockchain >  Cast variable changes variable name - SQL
Cast variable changes variable name - SQL

Time:04-07

I'm a total newb to SQL and am confused about why the variable name is changing when I try to cast a variable as text rather than numeric.

I'm using RSQLite and I would like to extract a few columns from my dbi connection and cast a few of the variable as type text.

Here is my code:

head5_query <- dbSendQuery(con,"SELECT studyid,
candidategroup,
submissionmonth,
submissionyear,
CAST(membercounty AS TEXT),
CAST(serviceprovidercounty AS TEXT),
CAST(admissionsource AS TEXT)
FROM medicalClaims WHERE studyid IS NOT NULL AND dateofservicetomonth = 1 AND dateofservicetoyear = 2011 LIMIT 5")
head_query = dbFetch(head5_query,n = -1)
head_query

which gives me:

> head_query
    studyid candidategroup submissionmonth submissionyear CAST(membercounty AS TEXT) CAST(serviceprovidercounty AS TEXT)
1 145311.10              1               2           2014                    Bristol                             Suffolk
2  36458.10              1               5           2014                    Suffolk                             Suffolk
3  12403.17              1               2           2014                       <NA>                             Suffolk
4  12403.16              1               2           2014                       <NA>                             Suffolk
5  12403.15              1               2           2014                       <NA>                             Suffolk
  CAST(admissionsource AS TEXT) medicalclaimid apcdidcode
1                          <NA>       22778326          4
2                           2.0       46829274          0
3                           1.0       64643334          0
4                           1.0       64643334          0
5                           1.0       64643334          0

Where the variable name has changed to CAST(membercounty AS TEXT), for example. However, it appears that variable type has changed to text:

> class(head_query$`CAST(admissionsource AS TEXT)`)
[1] "character"

How do I change the variable type without changing the variable name?

Thanks so much!

CodePudding user response:

You need to ALIAS your columns (they're not variables in your SELECT statement) when using a function to modify them. https://www.tutorialspoint.com/sqlite/sqlite_alias_syntax.htm

  • Related