Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

fill does not work with SQL server #651

Closed
nassuphis opened this issue May 7, 2021 · 4 comments · Fixed by #677
Closed

fill does not work with SQL server #651

nassuphis opened this issue May 7, 2021 · 4 comments · Fixed by #677

Comments

@nassuphis
Copy link

fill does not work with SQL server

> tibble(x=c(1,2,3,NA,5,6,NA,NA),i=seq_along(x)) %>%
+ copy_to(name="test",dest=db_con,temporary=FALSE,overwrite=TRUE,append=FALSE) %>%
+ window_order(i) %>%
+ fill(x)  
Error: nanodbc/nanodbc.cpp:1655: 00000: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'IGNORE'.  [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. 
<SQL> 'SELECT TOP 11 "i", LAST_VALUE("x" IGNORE NULLS) OVER (ORDER BY "i") AS "x"
FROM "test"'
@mgirlich
Copy link
Collaborator

mgirlich commented May 7, 2021

Ah, it seems the IGNORE NULLS part should be outside of LAST_VALUE(). Can you check if your code works after you executed the following code

`last_value_sql.Microsoft SQL Server` <- function(con, x) {
  build_sql("LAST_VALUE(", ident(as.character(x)), ") IGNORE NULLS", con = con)
}

@nassuphis
Copy link
Author

will do + revert.
the whole dbplyr package is awesomely useful.
thanks for the all the good work!

@mgirlich
Copy link
Collaborator

mgirlich commented Jul 7, 2021

Argh, seems like IGNORE NULLS is only supported by Azure SQL Edge

@fabkury
Copy link

fabkury commented Dec 22, 2022

Same thing happens with Databricks ODBC connections:

Error in new_result(connection@ptr, statement, immediate) : 
  nanodbc/nanodbc.cpp:1412: 42000: [Simba][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: org.apache.hive.service.cli.HiveSQLException: Error running query: org.apache.spark.sql.catalyst.parser.ParseException: 
mismatched input 'IGNORE' expecting {')', ',', '-'}(line 21, pos 38)

SELECT
      `row_id`,
      `pid`,
      `ts`,
      LAST_VALUE(`guia_total_cobrado` IGNORE NULLS) OVER (PARTITION BY `pid` ORDER BY `pid`, `ts`) AS `guia_total_cobrado`,
--------------------------------------^^^

I was able to make it work by running this prior to fill():

`last_value_sql.Spark SQL` <- function(con, x) {
  dbplyr:::build_sql("LAST_VALUE(", ident(as.character(x)), ", true)", con = con)
}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants