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")
Improve this question
4
  • 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. Commented Sep 28, 2022 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.) Commented Sep 28, 2022 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.. Commented Sep 29, 2022 at 8: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. Commented Sep 29, 2022 at 13:33
Related questions
1
SQL query error with ODBC connection in R using Informix driver
1
Using R object in DBgetquery
6
sqlquery in R does not return all rows from query
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.