0

I am attempting to query an Oracle datebase from R using the SQL function found here.

When I complete an easy query, such as 

'SELECT * FROM TABLE_1'

the query executes fine. However, when I add a conditional date statement to the query, such as

'SELECT * FROM TABLE_1 WHERE START_DT BETWEEN '01-JUL-2018' AND '30-JUN-2019'

I get the following error message:

Error in .oci.SendQuery(conn, statement, data = data, prefetch = prefetch, : ORA-00904: "30-JUN-2019": invalid identifier

Any idea how I can fix this?

djc55
  • 487
  • 2
  • 8

1 Answers1

2

The exact error appears to be that you didn't escape the single quotes you placed around the date literals in your R query string. But, fixing that still leaves the problem that your date literals are invalid for Oracle. I recommend using this:

sql <- "SELECT * FROM TABLE_1 WHERE START_DT BETWEEN DATE '2018-07-01' AND DATE '2019-06-30'"

You could also use the TO_DATE function, e.g. TO_DATE('01-JUL-2018', 'DD-MON-YYYY'), but this is a bit harder to read than using the DATE keyword.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • 1
    It was a mistake, assume the DATE column is in reality named START_DT. Thanks for flagging; This was sloppy question writing. Apologies – djc55 Aug 12 '19 at 14:24
  • @Parfait `I am attempting to query an Oracle datebase` ... date literals in Oracle have their own rules. – Tim Biegeleisen Aug 12 '19 at 14:25
  • @Parfait the OP should avoid using a column name of "DATE" for just this reason. The "DATE" keyword has meaning in Oracle, and should therefore be avoided when thinking up identifier names. – Boneist Aug 12 '19 at 14:25
  • Spoke too soon. Any idea how to handle aliases? It seems like when I have quotes within quotes, see below, my query breaks :/ sql <- "SELECT PROG_ID AS "PROGRAM" FROM TABLE_1 WHERE START_DT BETWEEN '01-JUL-2018' AND '30-JUN-2019''" – djc55 Aug 12 '19 at 14:36
  • 1) `PROGRAM` is not an Oracle keyword, so it does not require escaping in double quotes, and 2) if it did, then you just need to escape the double quotes in your R string with backslash, e.g. `\"PROGRAM\"` – Tim Biegeleisen Aug 12 '19 at 14:43
  • That makes sense. Thanks. I'll just omit all single quotes in the original query for aliases. Usually keep them in there as that changes their color in our Oracle DB software (Toad) so it makes it easier to read in that environment, but seems like they don't play well with R. Thanks again for the help Tim! – djc55 Aug 12 '19 at 14:54