0

When I run a sql query from HANA in R pulling all columns, the data returns correctly.

sql <- "SELECT TOP 10 *
        FROM \"ccf-edw.self-service.DOIP::R_CA_B_DemandPlan\"( 
            'PLACEHOLDER' = ('$$IP_ExtractionWeekFrom$$', '201943'), 
            'PLACEHOLDER' = ('$$IP_ExtractionWeekTo$$', '201943')) "

SpringVisitAll <- dbGetQuery(jdbcConnection, sql)

This gives me columns with actual data.

However if I try and call only a few columns, the data is filled with just the column names

sql <- "SELECT TOP 10 'SalesOffice', 'CalendarMonth'
        FROM \"ccf-edw.self-service.DOIP::R_CA_B_DemandPlan\"( 
            'PLACEHOLDER' = ('$$IP_ExtractionWeekFrom$$', '201943'), 
            'PLACEHOLDER' = ('$$IP_ExtractionWeekTo$$', '201943')) "

SpringVisitAll <- dbGetQuery(jdbcConnection, sql)

Gives two columns where the contents of each reads SalesOffice and CalendarMonth all the way down.

What's going on here?

jarichardson
  • 165
  • 8
  • 5
    Please check the rule of SAP Hana database for referencing column names. This `'SalesOffice'` is a *literal* not a *column reference*. Try to remove quotes or use (escaped) double quotes if required ro reference mixed case columns names. – Marmite Bomber Oct 30 '19 at 12:25
  • 2
    As Marmite Bomber says, when you have `'SalesOffice'` in your query, it is a string literal, not a column reference. So the result is expected for the query. You need to use a column reference (eg `SalesOffice` or maybe `"SalesOffice"`). – Mark Rotteveel Oct 30 '19 at 12:32
  • 1
    This is related to your [last question](https://stackoverflow.com/q/58610539/1422451) which I [commented](https://stackoverflow.com/questions/58610539/r-pass-required-variable-from-odbc-hana-connection-to-sql-statement#comment103533530_58610539). Double quotes and single quotes mean different things in SQL as opposed to being synonymous in R. Do not confuse the two. – Parfait Oct 30 '19 at 14:19
  • 2
    Marmite Bomber is correct and the comment should be an answer! – Lars Br. Oct 31 '19 at 01:39

1 Answers1

0

Base on the Naming Conventions for SAP HANA

Names are not case sensitive. For example, CUSTOMER and Customer are the same, but object names are converted to uppercase when they are stored in the SAP HANA database. However, if you enclose a name in quotation marks, it is case sensitive.

So dependent on how you defined the column in the CREATE TABLE statement (with or without quotation marks) you have two options to reference them

Unquoted Column Name - Case Insensitive

Simple write the column name without delimiters:

 sql <- "SELECT TOP 10  SalesOffice ,  CalendarMonth  ...

Double Quoted Column Name - Case Sensitive

You must enclose the column name in double quotes. Additionally in a double quoted R string you must escape the double quotes using \"

 sql <- "SELECT TOP 10  \"SalesOffice\" ,  \"CalendarMonth\"  ...
Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53