Menu
I am executing below commands in R:
dbhandle <- odbcDriverConnect('driver={SQL Server};server=serveripaddress;database=DBName;uid=sa;pwd=pwd;')
FactActivity <- sqlQuery(dbhandle, "SELECT DimCourseID,DimPatientID FROM DWH.FactActivity", as.is=TRUE)
nrow(FactActivity)
In my database I have total 238634 rows but in R I am getting total rows = 237652.
This is happening for all tables (I tried it for three tables).
Any idea what am I missing which is reducing no of rows in my resultset in R?
480
Answer. Answer: SELECT query is used to retrieve data from a table. It is the most used SQL query. We can retrieve complete table data, or partial by specifying conditions using the WHERE clause.
You can use the FETCH FIRST 1 ROW ONLY clause in a SELECT statement to ensure that only one row is returned. This action prevents undefined and unpredictable data from being returned when you specify the INTO clause of the SELECT statement.
So there is no limit to the number of rows that a ResultSet can contains, nor to the number of rows that a java client program can process. The only limit comes if you try to load all the rows in memory to populate a list for example and exhaust the client application memory.
SELECT * FROM <TableName>; This SQL query will select all columns and all rows from the table. For example: SELECT * FROM [Person].
That said, if you eliminate filter criteria from your query and still return no rows, then you know the problem is in your joins. Could be you need a LEFT or RIGHT join someplace instead of all INNERs. IF you suddenly do start returning rows, then your filters are the problem.
Simply paste your SQL code into the R function as a quoted string. This method is sometimes referred to as pass through SQL code, and is probably the simplest way to query your data. Care should be used to escape your quotes as needed. For example, 'yes' is written as \'yes\'.
- SQL Server Forum Why Query is not returning any results? First and foremost, the following items are a test of something bigger. The following is a create table script for the tables, cestest and cescode. Followed by a some sample data for both. Lastly, is the query that I am trying to run. I get column headers but no results.
To use SQL, open an R Notebook in the RStudio IDE under the File > New File menu. Start a new code chunk with {sql}, and specify your connection with the connection=con code chunk option.
I had the same problem, couldn't solve it, eventually switched to using RJDBC with a Java driver to get around it.
76
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
