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