0

I am trying to use the SQL query to count the number of rows of myid in Table_1 data frame. The data frame is already present in R Environment and I retrieved it by following query.

Table_1 <- dbGetQuery(conn, "Select * FROM Patients") .

Now to count() the number of rows of myid in Table_1, I use following query and it says

  count_myid <- dbGetQuery(conn, "Select count(myid) FROM Table_1")


    Invalid object name 'Table_1'[Microsoft][odbc sql server driver]
[sql server] Statement(s) could not be prepared

Whereas, I checked the above mentioned query for another Table and it works perfectly fine for another table. I spent hours on this to figure out the problem but I couldn't that what is actually wrong in this query and why its not working. I know it can be done using different functions of R. I did used dbReadTable() and all the same queries mentioned in this Question, but not working.

If I try to retrieve it directly from SQL database and not from the R Environment, then it works fine. From SQL DB, I use the following query.

count_myid <- dbGetQuery(conn, "Select count(myid) FROM MyProj.dbo.Patients")
Usman YousafZai
  • 1,088
  • 4
  • 18
  • 44
  • 1
    *it works perfectly fine for another table* ... maybe you have a table with same name as data frame? As coded right now, `dbGetQuery` only uses the `conn` connection, so queries cannot see anything in your R environment. – Parfait Sep 28 '22 at 15:47
  • 1
    If you want to use SQL on a local (in R only) object, use the `sqldf` package: `sqldf::sqldf("select count(myid) FROM Table_1")`. (Note that `sqldf` is not going to know about `Patients`, since that table is not local.) – r2evans Sep 28 '22 at 19:39
  • @Parfait No I don't have any other table with the same name. I cleared the R environment as well to see and find where the problem is . But it is very weird that it works for other tables and not for the one I mentioned. Does this had to do something with table data ? The table composed around 25GB of data.. – Usman YousafZai Sep 29 '22 at 08:04
  • 1
    Forgive me but I find the *works for other tables* hard to believe. Please post all `library` commands to know your actual DB API used and please tag your RDBMS type. AFAIK `DBI::dbGetQuery` in major APIs (RPostgreSQL, odbc, RSQLite, RMariaDB, ROracle) cannot read from R environment but only objects from `conn` DB connection. Please *carefully* compare _all_ tables and views in all schemas: `SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_SCHEMA, TABLE_NAME`. Do not go off memory but actually check. – Parfait Sep 29 '22 at 13:33

0 Answers0