0

I am using R to link HANA, so that I can use SQL query in R for data retrieve. My data includes a lot of store names. sometimes, my query is based on all the stores, which can be done easily without setting up any limits in WHERE. Sometimes, I just focus on a couple of stores, like store 1,2,3. I can use the answer from Providing lookup list from R vector as SQL table for RODBC lookup to do this.

For example:

IDlist <- c(23, 232, 434, 35445)
paste("WHERE idname IN (", paste(IDlist, collapse = ", "), ")")

But how can I combine these two situations, i.e., all names or name subset, together in a WHERE? I am looking forward for something like:

IDlist <- all
IDlist <- c(23, 232, 434, 35445)
paste("WHERE idname IN (", paste(IDlist, collapse = ", "), ")")

So, when IDlist is all, then the query will be for all the store names. When IDlist have some specific numbers, then the query will just focus on those stores.

This is just my idea. I am not sure if there is a better way to do it. Anyway, combining all names and some names together so that I can use them in one WHERE, therefore, I do not need to change my code.

Here, my WHERE is :

myOffice <- c(416,247,602,428)
WHERE "a"."/BIC/ZARTICLE"<>\'GIFTCARDPU\' AND "a"."/BIC/ZRETURN"=\'X\'
      AND "a"."CALDAY" BETWEEN',StartDate,'AND',EndDate,' 
      AND "a"."/BIC/ZSALE_OFF" IN (',paste(myOffice, collapse = ", "),')
halfer
  • 19,824
  • 17
  • 99
  • 186
Feng Chen
  • 2,139
  • 4
  • 33
  • 62
  • Use an `if` statement. `if (IDlist == all)` omit the `where` clause entirely. – Gregor Thomas May 31 '17 at 04:23
  • Could you please be more specific. For example, my WHERE is: WHERE "date" BETWEEN',StartDate,'AND',EndDate,' AND "office" IN (',paste(myOffice, collapse = ", "),'). StartDate and EndDate are two R variable. paste(myOffice, collapse = ", ") is a R paste function. How can I put if in here? – Feng Chen May 31 '17 at 05:17
  • Put that full `where` clause in your question and I'll give an answer. – Gregor Thomas May 31 '17 at 05:43

1 Answers1

1

Maybe something like this:

office_clause = ""
if (IDlist != all) {
    office_clause = paste(
        'AND "a"."/BIC/ZSALE_OFF" IN (',
        paste(IDlist, collapse = ', '),
        ')'
    )
}

Then you can construct your query, just pasting office_clause at the end of the where. If it IDlist is all, then you will paste on a blank string, otherwise you will paste on the ID clause. (Note that I assume all is a variable because that's how you used it in the question.)

Gregor Thomas
  • 136,190
  • 20
  • 167
  • 294