3

I am using dbGetQuery() function to query the Oracle and put results into a data frame in R. I have connected to database successfully, and returned data using dbGetQuery() with simple queries. However, I have a query looks like:

Query <- paste("With A as (SELECT * from Table A),
          B as (SELECT * from Table B),
          C as (SELECT * from Table C)
          SELECT A.a,B.b,C.c
          FROM C
          LEFT OUTER JOIN A on (...)
          LEFT OUTER JOIN B on (...);")
Data <- dbGetQuery(conn, Query)

I have run this query in Oracle and it works well, and returned a table with all data from Table C and data from Table A and B qualified with on statement. When I paste this query to R, and use dbGetQuery() function, I got nothing but a error warning saying:

Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ",  : 
Unable to retrieve JDBC result set for With Albemarle_Data AS

I am wondering if anyone met similar problem before, and would be grateful if I can get any solution from you. Thank you.

Vladimir Vagaytsev
  • 2,871
  • 9
  • 33
  • 36
Xuefei Hou
  • 73
  • 6
  • sometime i have to use `dbQuoteIdentifier( conn , "columname")` and `dbQuoteString( conn , "char")` to ge tthe good column name and variable value in query or maybe you should use `WITH` instead of `With` – s.brunel Aug 26 '16 at 12:02
  • Thx for your answer, I have changed `with` to `WITH` but it does not work. I don't understand `dbQuoteIdentifier( conn , "columname")` and `dbQuoteString( conn , "char")` well after checking the help documatation, would you like to explain how to use it more detailed? Thank you – Xuefei Hou Aug 26 '16 at 12:19
  • something like `query <- paste0( "SELECT " , dbQuoteIdentifier(conn,"c" ) , "FROM C")` it will surround c with "\ \" – s.brunel Aug 26 '16 at 12:27

0 Answers0