RODBC No data available in table

Question

I use RODBC to get data from sql

sql <- paste0("
              with cte as (
Select *,datePart(WEEKDAY,Dt) as WeekDay,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY SaleCount) Over (partition by ItemRelation,
DocumentNum, DocumentYear) as PERCENTILE,
avg(SaleCount) over (Partition by ItemRelation, 
DocumentNum, DocumentYear,datePart(WEEKDAY,Dt), IsPromo) as AVG_WeekDay
From [Action].[dbo].[promo_data_copy])
Update a 
Set SaleCount = cte.AVG_WeekDay
From CTE
join [Action].[dbo].[promo_data_copy] a 
  on a.Dt = cte.dt
 and a.ItemRelation=cte.ItemRelation 
 and a.DocumentNum = cte.DocumentNum 
 and a.DocumentYear = cte.DocumentYear 
 and a.ispromo = cte.ispromo
Where CTE.PERCENTILE < CTE.SaleCount
and datePart(WEEKDAY,CTE.Dt) < 5
and CTE.ispromo = 0 ;")



df <- sqlQuery(dbHandle, sql)
View(df)

and df is empty dataset. No data available in table

Can anobody help me understand, why the data wasn't return?

Edit

Dt  ItemRelation    SaleCount   DocumentNum DocumentYear    IsPromo
2017-10-12 00:00:00.000 13322   7   36  2017    0
2017-10-12 00:00:00.000 13322   35  4   2017    0
2017-10-12 00:00:00.000 158121  340 41  2017    0
2017-10-12 00:00:00.000 158122  260 41  2017    0
2017-10-13 00:00:00.000 13322   3   36  2017    0
2017-10-13 00:00:00.000 13322   31  4   2017    0
2017-10-13 00:00:00.000 158121  420 41  2017    0
2017-10-13 00:00:00.000 158122  380 41  2017    0
2017-10-14 00:00:00.000 11592   45  33  2017    0
2017-10-14 00:00:00.000 13189   135 33  2017    0
2017-10-14 00:00:00.000 13191   852 33  2017    0
2017-10-14 00:00:00.000 13322   1   36  2017    0
2017-10-14 00:00:00.000 13322   34  4   2017    0
2017-10-14 00:00:00.000 158121  360 41  2017    0
2017-10-14 00:00:00.000 158122  140 41  2017    0

here top 15 observations of table.So i expect that my query will return me this data.frame

1. Just FYI, `RODBC` is an old, very slow alternative to `odbc` so you might want to switch. 2. Can you provide more info, like show the result of "select top 10 *" from each source table? — Hack-R, Jun 29 '18 at 12:15
@Hack-R, i edited post with select top. Please check it. what library or alternativa i should use — psysky, Jun 29 '18 at 12:20

score 0 · Answer 1 · answered Jul 17 '18 at 20:59

I'm not sure about the percentile stuff; I'll leave it to you to get that part straightened out. Anyway, here is how I use R to query a database.

library(RODBC)
dbconnection <- odbcDriverConnect("Driver=ODBC Driver 11 for SQL Server;Server=Server_Name; Database=DB_Name;Uid=; Pwd=; trusted_connection=yes")
initdata <- sqlQuery(dbconnection,paste("select * from MyTable;"))
odbcClose(channel)

Here are a couple very useful resources for cross-reference.

http://stackoverflow.com/questions/15420999/rodbc-odbcdriverconnect-connection-error

https://andersspur.wordpress.com/2013/11/26/connect-r-to-sql-server-2012-and-14/

RODBC No data available in table

Edit

1 Answers1