using a string object within a SQL query

General
,
1

I am using the Rpostgres package to run SQL queries

My code looks like this:

geographies <- tbl( 
  connection,
  dbplyr::sql_query_select(
    connection,
    sql("*"),
    dbplyr::translate_sql(
      get_geographies(get_granularity_id('geo.place'), 'country:us/state:ca/place:los_angeles_city')
    )
  )
)

'country:us/state:ca/place:los_angeles_city' represents the geography that I would like to pull data for.... but since I am running this and other queries for multiple geographies, I would like to be able to replace it with s
something like this so that I can easily change out the geography with one line of code as opposed to editing multiple queries

shid <- 'country:us/state:ca/place:los_angeles_city'

geographies <- tbl( 
  connection,
  dbplyr::sql_query_select(
    connection,
    sql("*"),
    dbplyr::translate_sql(
      get_geographies(get_granularity_id('geo.place'), shid)
    )
  )
)

but I get this error

Error: Failed to prepare query: ERROR:  syntax error at or near ")"
LINE 4: ...ROM get_geographies(get_granularity_id('geo.place'), `shid`)
                                                                      ^

I have tried using shid and {shid} but neither of those change things

any help would be GREATLY appreciated!!!!!!

2

Does it help using local()?

e.g.

get_geographies(get_granularity_id('geo.place'), local(shid))

from here.

3

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.