R dbplyr SQL Error "expecting something between Database_Name and LIMIT"

Question

Here's the connection we're working with:

con <- DBI::dbConnect(odbc::odbc(),
                      Driver = "[your driver's name]",
                      Host   = "[your server's path]",
                      DBCName = "[IP Address or Alias]"
                      UID    = rstudioapi::askForPassword("Database user"),
                      PWD    = rstudioapi::askForPassword("Database password"))

The command below works fine in my code, with no errors, and output that appears correct:

table1 <- tbl(con, "SAP_PRD_QMEL_ACQ")

However, when I run this line of code I get the following error:

table2 <- DBI::dbGetQuery(con, "SELECT * FROM SAP_PRD_QMEL_ACQ LIMIT 5")

#> Error in new_result(connection@ptr, statement) : 
#>   nanodbc/nanodbc.cpp:1344: 42000: [Teradata][ODBC Teradata Driver]
#> [Teradata Database](-3706)Syntax error: expected something between the word 
#> 'SAP_PRD_QMEL_ACQ' and the 'LIMIT' keyword.

When I create simple repro's to try and duplicate the error I come up empty handed. Why would I be getting this "expecting something between Database_Name and LIMIT" error?

ErrorJordan · Accepted Answer · 2019-12-06 22:11:45Z

2

Not going to be able to reproduce your issue, but I believe you will need to use TOP instead of LIMIT:

table2 <- DBI::dbGetQuery(con, "SELECT TOP 5 * FROM SAP_PRD_QMEL_ACQ")

answered Dec 6, 2019 at 22:11

ErrorJordan

6415 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Display name Over a year ago

and that did indeed work! Why though? What probably happened? Am decent in R but don't know much about SQL to troubleshoot this much.

ErrorJordan Over a year ago

There is slightly different SQL syntax depending on the type of database you're hitting, etc. Just a nuance you'll get used to checking as you keep experiencing it.

ravioli Over a year ago

A side note -- if you're going to use TOP N without an ORDER BY you can also use the SAMPLE modifier - SELECT * FROM SAP_PRD_QMEL_ACQ SAMPLE 5

Collectives™ on Stack Overflow

R dbplyr SQL Error "expecting something between Database_Name and LIMIT"

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related