0

I am using the below code in R but I have a small problem I can't find the solution. Because I will have many WHERE TE_ID = *** AND TE_ID = *** AND ... AND I am trying to find a simple way to assign the numbers (02343) to variables, if it is possible out of the query.

So in practice, I have 300 different TE_ID. What is a way to avoid adding 100 AND, or any other way that will help me

jdbcDriver <- JDBC(driverClass="oracle.jdbc.OracleDriver", classPath="lib/ojdbc6.jar")
jdbcConnection <- dbConnect(jdbcDriver, "jdbc:oracle:thin:@//database.hostname.com:port/service_name_or_sid", "username", "password")
MyData <- dbGetQuery(jdbcConnection,"SELECT VALUE,DATA_POINT_DATE
FROM HD.HD_SE_HAM 
WHERE TE_ID = 02342
and end_date = to_date('31/12/9999', 'dd/mm/yyyy')
and DATA_SKATA_DATE  Between '01-Jan-10 ' and  '30-Jun-13'
order by data_skata_date asc")
EnexoOnoma
  • 8,454
  • 18
  • 94
  • 179
  • 2
    Could you provide a little more information about `TE_ID`? What is the range of the numbers? Are they sequential? – joemienko Aug 11 '14 at 08:24
  • Some yes but some no. It would be great if you provide me at least fore these that are sequentials. – EnexoOnoma Aug 11 '14 at 08:30
  • 1
    Can't you just `paste` a string together? `paste(1:10, "and")`. – Roman Luštrik Aug 11 '14 at 08:34
  • 1
    One more thing, when you say you want to "assign the numbers to variables", do you mean that you want to have columns (i.e. variables) labeled with the `TE_ID` or do you have one `TE_ID` variable that you want to be populated with your results. In other words, do you want your results to be long or wide? – joemienko Aug 11 '14 at 08:36
  • @joemienko No, sorry that I didn't clarify that. I want them to be wide – EnexoOnoma Aug 11 '14 at 08:42
  • @joemienko maybe with a loop ? – EnexoOnoma Aug 11 '14 at 10:49
  • A loop would work (along with a `paste()` function), but a more elegant solution might be to just lean on your MySQL server and produce a dynamic pivot table - something like what was implemented [here](http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns) – joemienko Aug 11 '14 at 16:01

0 Answers0