From Clause in RODBC Quotations - SAP HANA

Question

I cannot seem to get the below FROM clause to work using RODBC's sqlQuery. I have taken the advice of @Lars Br. in terms of the quoting but it still does not work. I am

I know the placeholder piece works as I have used this in qlikview

Extracting Table from HANA using R

So the below code works

table <- sqlQuery(myconn, 'SELECT *
                      FROM "_SYS_BIC"."mytable.TABLE/ALL_DATA"')

But when I try to add the following (I am passing my date parameters in)

table <- sqlQuery(myconn, 'SELECT *
                      FROM "_SYS_BIC"."mytable.TABLE/ALL_DATA"')   
                    ('PLACEHOLDER' = ('$$AS_OF_DATE$$',
                     '2017-01-09'),
                    'PLACEHOLDER' = ('$$ABCD_ONE$$',
                     '0'),
                    'PLACEHOLDER' = ('$$ABCD_TWO$$',
                     '0'),
                     'PLACEHOLDER' = ('$$EFGH$$',
                    '12345'),
                    'PLACEHOLDER' = ('$$FLAG$$',
                      '1'))')

Now I know that my single quotes are what is messing it up so I tried the following.

1. replaced all the single quotes with double quotes - This did not work
2. encapsulated all the single quotes in double quotes
3. removed all the single quotes completely.

Answer 1

Here you fall into the traps of nested syntax and multi-level statement processing:

table <- sqlQuery >(< myconn, 'SELECT *
                  FROM "_SYS_BIC"."mytable.TABLE/ALL_DATA"'>)< ---!   
                ('PLACEHOLDER' = ('$$AS_OF_DATE$$',
                 '2017-01-09'),
                'PLACEHOLDER' = ('$$ABCD_ONE$$',
                 '0'),
                'PLACEHOLDER' = ('$$ABCD_TWO$$',
                 '0'),
                 'PLACEHOLDER' = ('$$EFGH$$',
                '12345'),
                'PLACEHOLDER' = ('$$FLAG$$',
                  '1'))')

For R your command ends where I put the < ---! and all the rest is invalid to R.
In this case it's important to recall that the sqlQuery() function expects the whole SQL command string in the second parameter. This includes the WITH PARAMETER syntax of HANA.

To avoid such issues - would be to assign the SQL command to a variable first and only use the variable in the function call.
In order to set the parameters with R variables, you could use text substitution.

# create the date parameter in the right format YYYY-MM-DD
selDate <- format(Sys.Date() , "%F")

selDate [1] "2017-02-04"

# create the base SQL command with %D as a placeholder for the selDate
# note how all single quotes inside the sqlCMD need to be escaped by a \
 sqlCMD <- 'SELECT *
 +                   FROM "_SYS_BIC"."mytable.TABLE/ALL_DATA"   
 + (\'PLACEHOLDER\' = (\'$$AS_OF_DATE$$\', \'%D\'),
 + \'PLACEHOLDER\' = (\'$$ABCD_ONE$$\', \'0\'),
 + \'PLACEHOLDER\' = (\'$$ABCD_TWO$$\', \'0\'),
 + \'PLACEHOLDER\' = (\'$$EFGH$$\', \'12345\'),
 + \'PLACEHOLDER\' = (\'$$FLAG$$\', \'1\'))'

sqlCMD [1] "SELECT *\n+ FROM \"_SYS_BIC\".\"mytable.TABLE/ALL_DATA\" \n+ ('PLACEHOLDER' = ('$$AS_OF_DATE$$', '%D'),\n+ 'PLACEHOLDER' = ('$$ABCD_ONE$$', '0'),\n+ 'PLACEHOLDER' = ('$$ABCD_TWO$$', '0'),\n+ 'PLACEHOLDER' = ('$$EFGH$$', '12345'),\n+ 'PLACEHOLDER' = ('$$FLAG$$', '1'))"

# now subsitute the %D with the selDate
sqlCMD <- gsub ("%D", selDate, sqlCMD)

sqlCMD [1] "SELECT *\n FROM \"_SYS_BIC\".\"mytable.TABLE/ALL_DATA\" \n('PLACEHOLDER' = ('$$AS_OF_DATE$$', '2017-02-04'),\n'PLACEHOLDER' = ('$$ABCD_ONE$$', '0'),\n'PLACEHOLDER' = ('$$ABCD_TWO$$', '0'),\n'PLACEHOLDER' = ('$$EFGH$$', '12345'),\n'PLACEHOLDER' = ('$$FLAG$$', '1'))"

  # finally run the query
  table <- sqlQuery(myconn, sqlCMD)

Of course, all the general recommendations (like not to use SELECT * or to ensure correct filtering and aggregation before fetching the result data set) apply.

Answer 2

Alright so @Lars Br. You are answer was the first piece of the puzzle the second piece was escaping the single quotes. Below is the SQLcmd variable that work as well as the sqlQuery function in action. Thanks again!

The below link helped me understand the concept.

Error: unexpected symbol/input/string constant/numeric constant/SPECIAL in my code

sqlCMD <- 'SELECT *
              FROM "_SYS_BIC"."mytable.TABLE/ALL_DATA"
              (\'PLACEHOLDER\' = (\'AS_OF_DATE$$\',\'2017-01-09\'),
              \'PLACEHOLDER\' = (\'$$ABCD_ONE$$\',\'0\'),
              \'PLACEHOLDER\' = (\'$$ABCD_TWO$$\',\'0\'),
              \'PLACEHOLDER\' = (\'$$EFGH$$\',\'123456\'),
              \'PLACEHOLDER\' = (\'$$FLAG$$\',\'1\'))'

table <- sqlQuery(myconn, sqlCMD)